Generate a table of missig values using DAX

[dosc_21]

Hi,

I'm new to Power BI and DAX, and I'm trying to generate a table listing missing values between ranges in the same group

GROUP	START	END
A	2	5
A	7	9
A	12	15
B	44	48
B	51	57
B	60	63

My result table should be 

GROUP	MISSING VALUES
A	6
A	10
A	11
B	49
B	50
B	58
B	59

[parry2k]

@Greg_Deckler even if I give the table name it doesn't resolve, although I should have:

[parry2k]

@Greg_Deckler seems like DAX editor issue, same for me. They are the columns in the table:

[Greg_Deckler]

@parry2k Weird those don't resolve but the code still works!

[parry2k · Accepted solution]

@dosc_21 check this DAX query to generate the new table, I hope it performs well

Missing Value = 
VAR __groupActualRange = 
    SELECTCOLUMNS ( 
        GENERATE ( 
            'Table', 
            GENERATESERIES ( 
                [START], 
                [END] 
            ) 
        ), 
        "Group", [GROUP], 
        "Values", [Value] 
    )
VAR __groupFullRange = 
    GENERATEALL ( 
        VALUES ( 'Table'[GROUP] ),  
        GENERATESERIES (  
            CALCULATE ( MIN ( 'Table'[START] ) ), 
            CALCULATE ( MAX ( 'Table'[END] ) ), 
            1 
        ) 
    )

RETURN 
EXCEPT ( __groupFullRange, __groupActualRange )

and here is the table output:

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[dosc_21]

@parry2k It performed very well!! Now I'll try to include another grouping parameter!  Thanks a lot!!

[Greg_Deckler]

@parry2k What are [START] and [END] in the first GENERATESERIES because when I paste them in they don't resolve. 

[parry2k]

@dosc_21 I have few meetings in the next few hours, I will tweak my measure and in the meantime, you can try to have a crack on it, core logic is already there, so shouldn't be that complicated.

[parry2k]

@dosc_21 I have few meetings in the next few hours, I will tweak my measure and in the meantime, you can try to have a crack on it, core logic is already there, so shouldn't be that complicated.

[parry2k]

@dosc_21 and that's what my concern was going to be and I wanted you to test @Greg_Deckler solution before I tweak my stuff. 

[parry2k]

@dosc_21 Sounds good. I guess @Greg_Deckler  already provided a solution. Just use that, if not, please let us know and we (I) can tweak my solution.

[dosc_21]

@parry2k I tested the other solution, it worked in a smaller table, but the application crashed when I tested with a large amount of data 

[parry2k]

@dosc_21 this is what I will do in case you don't want to create a separate table, I will just add a following measure and use this in the table visual:

Missing Value = 
VAR __groupTable = FILTER ( ALL ( 'Table' ), 'Table'[GROUP] = MAX ( 'Table'[GROUP] ) )
VAR __groupStartValue = MINX ( __groupTable, [START] )
VAR __groupEndValue = MAXX ( __groupTable, [END] ) 
VAR __groupFullRange = GENERATESERIES ( __groupStartValue, __groupEndValue, 1 )
VAR __groupActualRange = SELECTCOLUMNS ( GENERATE ( __groupTable, GENERATESERIES ( [START], [END] ) ), "Values", [Value] )
RETURN 
CONCATENATEX ( 
    EXCEPT ( __groupFullRange, __groupActualRange ),  
    [Value], 
    "," 
) 

Output:

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[dosc_21]

@parry2k I'm working with a huge table with lots of gaps, so the result shoud be a table...

[Greg_Deckler]

@dosc_21 Try this, just replace 'Table5' with your source table name:

Table5a = 
    VAR __SourceTable = 'Table5'
    VAR __Table = 
        ADDCOLUMNS(
            __SourceTable,
            "__Series",CONCATENATEX(GENERATESERIES([START],[END],1),[GROUP]&":"&[Value],"|")
        )
    VAR __SeriesTable = SUMMARIZE(__Table,[GROUP],"__TotalSeries",CONCATENATEX(FILTER(__Table,[GROUP]=EARLIER([GROUP])),[__Series],"|"))
    VAR __CompleteSeries = CONCATENATEX(__SeriesTable,[__TotalSeries],"|")
    VAR __Count = PATHLENGTH(__CompleteSeries)
    VAR __ExistingTable = 
            SELECTCOLUMNS(
                ADDCOLUMNS(
                    ADDCOLUMNS(
                        GENERATESERIES(1,__Count,1),
                        "__Word",PATHITEM(__CompleteSeries,[Value],TEXT)
                    ),
                    "GROUP",LEFT([__Word],SEARCH(":",[__Word])-1),
                    "ID",RIGHT([__Word],LEN([__Word])-SEARCH(":",[__Word]))
                ),
                "GROUP",[GROUP],
                "Value",[ID]
            )
    VAR __SeriesTable1 = 
        ADDCOLUMNS(
            GROUPBY(__SourceTable,[GROUP],"START",MINX(CURRENTGROUP(),[START]),"END",MAXX(CURRENTGROUP(),[END])),
            "__Series",CONCATENATEX(GENERATESERIES([START],[END],1),[GROUP]&":"&[Value],"|")
        )
    VAR __CompleteSeries1 = CONCATENATEX(__SeriesTable1,[__Series],"|")
    VAR __Count1 = PATHLENGTH(__CompleteSeries1)
    VAR __PotentialTable = 
            SELECTCOLUMNS(
                ADDCOLUMNS(
                    ADDCOLUMNS(
                        GENERATESERIES(1,__Count1,1),
                        "__Word",PATHITEM(__CompleteSeries1,[Value],TEXT)
                    ),
                    "GROUP",LEFT([__Word],SEARCH(":",[__Word])-1),
                    "ID",RIGHT([__Word],LEN([__Word])-SEARCH(":",[__Word]))
                ),
                "GROUP",[GROUP],
                "Value",[ID]
            )
    VAR __ReturnTable = EXCEPT(__PotentialTable,__ExistingTable)
RETURN
    __ReturnTable

[dosc_21]

@Greg_Decklerthe solution worked very well in a scenario with less rows, but with the huge table I'm working it crashed