Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
dosc_21
New Member

Generate a table of missig values using DAX

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

 

GROUPSTARTEND
A25
A79
A1215
B4448
B5157
B6063


My result table should be 

GROUPMISSING VALUES
A6
A10
A11
B49
B50
B58
B59

 

 

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

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

 

parry2k_0-1631733257600.png

 

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.

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

15 REPLIES 15
parry2k
Super User
Super User

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

 

parry2k_2-1631734451302.png

 

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

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

 

parry2k_0-1631734317950.png

 

parry2k_1-1631734347057.png

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
parry2k
Super User
Super User

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

 

parry2k_0-1631733257600.png

 

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.

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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

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

Greg_Deckler_0-1631733912887.png

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
parry2k
Super User
Super User

@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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

@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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

@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. 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

@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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@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
Super User
Super User

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

 

parry2k_0-1631720175480.png

 

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.

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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

Greg_Deckler
Super User
Super User

@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

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.