Gaps And Islands With DAX

Philip Seamark answers one of the classic gaps and islands problems with DAX:

A recent post on the Power BI community website asked if it was possible to compress a group of numbers into text that described the sequential ranges contained within the numbers. This might be a group of values such as 1, 2, 3, 4, 7, 8, 9, 12, 13:  (note there are gaps) with the expected result grouping the numbers that run in a sequence together to produce text like “1-4, 7-9, 12-13”.  Essentially to identify gaps when creating the text.  This seemed like an interesting challenge and here is how I solved it using DAX.

Read on for the solution, which is conceptually very similar to the T-SQL solution but a bit different in implementation.

Related Posts

Aggregating Only Visible Values in DAX

Alberto Ferrari shows how you can calculate a sum based only on the visible values and ignoring the missing cases: These measures produce the correct figures month by month. However, at the year level the number of working days might be too big whenever there are incomplete months – this always happens before the end […]

Read More

Exporting Data from Power Query with R

Leila Etaati shows how you can use R to export data from Power Query to disk or to SQL Server: There is always a discussion on how to store back the data from Power BI to local computer or SQL Server Databases, in this short blog, I will show how to do it by writing […]

Read More

Categories

May 2018
MTWTFSS
« Apr Jun »
 123456
78910111213
14151617181920
21222324252627
28293031