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

5 Reasons To Like Power BI Composite Models

Devin Knight has a video explaining why it’s worth getting excited over Power BI Composite Models: Another special new capability that we gain with Composite Models is native support for many-to-many relationships. I see this as incredibly helpful to new Power BI users that are not familiar with strict requirements around relationships. Traditionally, Power BI […]

Read More

A Recommendation Against Using TOTALYTD

Marco Russo recommends against using the TOTALYTD function in DAX: You have been warned. I don’t like the code above. If you want to apply filters, use CALCULATE. Maybe the code written using CALCULATE is verbose and boring, but it’s easier to read. If you are looking for an enjoyable reading for your free time, you […]

Read More


May 2018
« Apr Jun »