cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Post Prodigy

## lookup ascending order

Hi,

I have three tables are Data, Report and Priority.

In data table the following columns are Id and order. (In data table id has duplicate entry)

In report table contain Id only . ( In report table contain unique entry)

Third table for priority of order.

In data table some of the id contain multiple orders and some of the id has not multiple order.

I am trying to pull the order against the id in report table based on the priority table.

In data table id has more than one order then need to be considered the priority table and if not then return the same thing in report table according to the id.

I am looking for new calculated column.

Example:

In data table TR1 id has R1 and TR1 to TR7, in this case my desired result is R1 because R1 is my first priority.

In data table TR2 has only T2 in this case my desired result is T2 because the id does not contain more than one order.

Data:

 ID Order TR1 T2 TR1 T1 TR1 T3 TR1 T4 TR1 T5 TR1 T6 TR1 R1 TR2 T1 TR3 T2 TR4 T3 TR5 T6 TR5 T4 TR5 T3 TR5 T1 TR5 T2

 PRIORITY TABLE Order Priority R1 1 T1 2 T2 3 T3 4 T4 5 T5 6 T6 7

 REPORT ID (Desired Result) Order TR1 R1 TR2 T1 TR3 T2 TR4 T3 TR5 T1 TR20 No

2 ACCEPTED SOLUTIONS
Super User

@Saxon10 use the following DAX to add a new column in the report table

``````Order =
VAR __tt = CALCULATE ( MAX ( Priority[Order] ), TOPN ( 1, RELATEDTABLE ( Data ), RELATED ( Priority[Order] ), ASC ) )
RETURN
COALESCE ( __tt, "No" )
``````

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.

Solution Specialist

You can create this relathionship structure

and this measure:

``````Order =
VAR RequiredValue =
CALCULATE (
TOPN ( 1, VALUES ( Priority[Order] ) ),
FILTER ( Priority, Priority[Priority] = MIN ( Priority[Priority] ) )
)
RETURN
IF ( RequiredValue <> BLANK (), RequiredValue, "No" )``````

to have this output:

8 REPLIES 8
Super User

@Saxon10 because the relationship is many to many and it will only work if it is one to many

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.

Post Prodigy

I am happy to maintain additional columns in my data, report or priority table. Could you please advise is there any alternative solutions.

Post Prodigy

DAX new calculate column option.

Post Prodigy

Both solutions working well.

Solution Specialist

You can create this relathionship structure

and this measure:

``````Order =
VAR RequiredValue =
CALCULATE (
TOPN ( 1, VALUES ( Priority[Order] ) ),
FILTER ( Priority, Priority[Priority] = MIN ( Priority[Priority] ) )
)
RETURN
IF ( RequiredValue <> BLANK (), RequiredValue, "No" )``````

to have this output:

Post Prodigy

Hi,

Sorry for disturbing you again. Can you please provide the DAX (New calculate column) solution instead of measure.

I am trying to apply parry solutions but some reason I am receving error.

Super User

@Saxon10 and assuming that you have a relationship between these tables as shown below:

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.

Super User

@Saxon10 use the following DAX to add a new column in the report table

``````Order =
VAR __tt = CALCULATE ( MAX ( Priority[Order] ), TOPN ( 1, RELATEDTABLE ( Data ), RELATED ( Priority[Order] ), ASC ) )
RETURN
COALESCE ( __tt, "No" )
``````

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.

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors