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:
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:
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 )