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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Peterdstraw
Frequent Visitor

Lookup ignoring values already used

Hello, I think what I'm trying to do may be impossible but thought it worth asking as a question to see if anyone can come up with a solution.

 

I have a table that looks like this:

 

ID1ID2
13
14
15
23
24
25
68
69
78

 

What I need to get to is a table that finds the minimum ID2 for each ID1 but with the constraint that no value of ID2 can be used more than once. This should be done in order of ID1, so ID1=1 takes precedence over ID1=2. If a value of ID2 is not available, the next lowest value should be chosen. So, the example would work out to:

ID1ID2
13
24
68
7NULL


I can't think of a way to do this using DAX. I think it has to be done by iterating over rows in a way that I'm not aware of being possible in DAX. Is that correct or is there a way to achieve this?

Thanks!

1 ACCEPTED SOLUTION
edhans
Super User
Super User

Hi @Peterdstraw , see if this works for you.
It turns this:

edhans_0-1600116437259.png

into this:

edhans_1-1600116463914.png

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTJWitWBsEzgLFMwywguawSXNYLLmgFZFnCWJZhlDhGLBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID1 = _t, ID2 = _t]),
    #"Grouped Rows" = 
        Table.Group(
            Source,
            {"ID1"}, 
            {
                {"MinValue", each List.Min(_[ID2])},
                {"AllRows", each _, type table [ID1=nullable text, ID2=nullable text]}
            }
        ),
    MinValue = 
        Table.AddColumn(
            #"Grouped Rows",
            "Minimum Value",
            each
                let
                    varCurrentID1 = [ID1],
                    varPreviousMin = Table.SelectRows(#"Grouped Rows", each [ID1] < varCurrentID1)[MinValue]
                in
                //varPreviousMin
                List.Min(
                    Table.SelectRows(
                        [AllRows], 
                        each not List.Contains(
                            varPreviousMin, 
                            [ID2]
                            )
                        )[ID2]
                    ),
                    type number
                    
        ),
    #"Removed Other Columns" = Table.SelectColumns(MinValue,{"ID1", "Minimum Value"})
in
    #"Removed Other Columns"

 

Because of the way this has to start at the top and rescan the entire table, I make no claims as to its performance once you pass 10,000 records, even grouping the way it does.

 

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

6 REPLIES 6
edhans
Super User
Super User

Hi @Peterdstraw , see if this works for you.
It turns this:

edhans_0-1600116437259.png

into this:

edhans_1-1600116463914.png

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTJWitWBsEzgLFMwywguawSXNYLLmgFZFnCWJZhlDhGLBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID1 = _t, ID2 = _t]),
    #"Grouped Rows" = 
        Table.Group(
            Source,
            {"ID1"}, 
            {
                {"MinValue", each List.Min(_[ID2])},
                {"AllRows", each _, type table [ID1=nullable text, ID2=nullable text]}
            }
        ),
    MinValue = 
        Table.AddColumn(
            #"Grouped Rows",
            "Minimum Value",
            each
                let
                    varCurrentID1 = [ID1],
                    varPreviousMin = Table.SelectRows(#"Grouped Rows", each [ID1] < varCurrentID1)[MinValue]
                in
                //varPreviousMin
                List.Min(
                    Table.SelectRows(
                        [AllRows], 
                        each not List.Contains(
                            varPreviousMin, 
                            [ID2]
                            )
                        )[ID2]
                    ),
                    type number
                    
        ),
    #"Removed Other Columns" = Table.SelectColumns(MinValue,{"ID1", "Minimum Value"})
in
    #"Removed Other Columns"

 

Because of the way this has to start at the top and rescan the entire table, I make no claims as to its performance once you pass 10,000 records, even grouping the way it does.

 

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Thanks so much for this @edhans - I really appreciate you taking the time.

 

I've got a little bit of figuring out to do to apply your code to my real dataset but it answers the original question on the toy dataset really well.

Glad to help @Peterdstraw - ping back if you have any questions.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Greg_Deckler
Super User
Super User

@Peterdstraw I am fairly certain that this would run into circular dependency in DAX. It has a recursive element to it (tracking previous value) which DAX is not going to like. Take a look at this article, it is the closest I have ever come:

https://community.powerbi.com/t5/Community-Blog/Previous-Value-Recursion-in-DAX/ba-p/638320

 

Would highly recommend using Power Query, which has true recursion capabilities:

https://www.linkedin.com/pulse/fun-graphing-power-bi-part-5-by-5-greg-deckler-microsoft-mvp-/


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thanks @Greg_Deckler.

Your article is helpful and your approach is pretty much where I had got to earlier today.

In my example you can remove all the rows where ID1 is the first ID1 for ID2 and at the same time ID2 is the first ID2 for ID1 using RANKX. Then with the remainder, rerun the ranks and do the same thing ad infinitum. It feels very ugly and I'd be worried about how future-proof it was as there may need to be more iterations for future data than there need to be for the data as it is today.

 

I will look into PowerQuery recursion but I'm very much a PowerQuery novice so I'll see how I get on!

@ImkeF @edhans can help with the Power Query.


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors