cancel
Showing results 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

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:

 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!

1 ACCEPTED SOLUTION
Super User

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 =
#"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

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
6 REPLIES 6
Super User

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 =
#"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

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
Frequent Visitor

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.

Super User

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

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

Thanks @Greg_Deckler.

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!

Super User

@ImkeF @edhans can help with the Power Query.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors