Lookup ignoring values already used

[Peterdstraw]

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:

ID1	ID2
1	3
1	4
1	5
2	3
2	4
2	5
6	8
6	9
7	8

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:

ID1	ID2
1	3
2	4
6	8
7	NULL

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!

[edhans · Accepted solution]

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

into this:

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.
 

[Peterdstraw]

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.

[edhans]

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

[Greg_Deckler]

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

[Peterdstraw]

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!

[Greg_Deckler]

@ImkeF @edhans can help with the Power Query.