Today I woke up with an interesting question, about how to show a selection of months in a nice way, detecting contiguous selection. You can easily understand the desired solution from the following figure:
I enjoyed writing a quick solution, which is worth sharing. The code is somewhat verbose, but this is mainly for educational purposes (meaning I did not want to spend time optimizing it). I will likely write a full article on it, for now, just enjoy some DAX code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 | Sel = VAR Months = VALUES ( 'Date'[Month Number] ) VAR MonthsPS = ADDCOLUMNS ( Months , "PrevSel" , 'Date'[Month Number] - 1 IN Months , "NextSel" , 'Date'[Month Number] + 1 IN Months ) VAR MonthsPSS = ADDCOLUMNS ( MonthsPS , "Seq" , SWITCH ( TRUE ( ) , [PrevSel] && [NextSel] , "Inside" , [NextSel] , "First" , [PrevSel] , "Last" , "Single" ) ) VAR MonthsPSSNoInside = FILTER ( MonthsPSS , [Seq] <> "Inside" ) VAR MonthsRanked = ADDCOLUMNS ( MonthsPSSNoInside , "ValPos" , RANKX ( MonthsPSSNoInside , [Month Number] , , ASC ) ) VAR MonthsWithNext = ADDCOLUMNS ( MonthsRanked , "NextMonth" , MAXX ( FILTER ( MonthsRanked , [ValPos] = EARLIER ( [ValPos] ) + 1 ) , [Month Number] ) ) VAR ValuesToShow = FILTER ( ADDCOLUMNS ( MonthsWithNext , "ValueToShow" , SWITCH ( [Seq] , "Single" , LOOKUPVALUE ( 'Date'[Month] , [Month Number] , [Month Number] ) , "First" , LOOKUPVALUE ( 'Date'[Month] , [Month Number] , [Month Number] ) & "-" & LOOKUPVALUE ( 'Date'[Month] , [Month Number] , [NextMonth] ) ) ) , [ValueToShow] <> "" ) VAR Result = CONCATENATEX ( ValuesToShow , [ValueToShow] , ", " , [Month Number] ) RETURN Result |
If you want to play with this code download the pbix file.
The web is a beautiful place! It didn’t take more than half a day, that Chris Webb proposed a much better solution in the comments (thanks Chris!), he provided the idea, I cleaned it a bit, and this new version looks way shorter, more readable and much more efficient. Because I know many people don’t read the comments, here is the new code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | Sel ( much better version ) = VAR SelectedMonthNumbers = VALUES ( 'Date'[Month Number] ) VAR MonthsAndNames = SUMMARIZE ( 'Date' , 'Date'[Month Number] , 'Date'[Month] ) VAR Result = CONCATENATEX ( MonthsAndNames , VAR CurrentMonthNumber = 'Date'[Month Number] VAR CurrentMonthName = 'Date'[Month] VAR IsNextSelected = CurrentMonthNumber + 1 IN SelectedMonthNumbers VAR IsPrevSelected = CurrentMonthNumber - 1 IN SelectedMonthNumbers RETURN IF ( NOT ( IsPrevSelected && IsNextSelected ) , CurrentMonthName & IF ( IsNextSelected , "-" , ", " ) ) , "" , 'Date'[Month Number] ) RETURN LEFT ( Result , LEN ( Result ) - 2 ) |