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

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

Reply
Saxon10
Post Prodigy
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:

IDOrder
TR1T2
TR1T1
TR1T3
TR1T4
TR1T5
TR1T6
TR1R1
TR2T1
TR3T2
TR4T3
TR5T6
TR5T4
TR5T3
TR5T1
TR5T2

 

PRIORITY TABLE
OrderPriority
R11
T12
T23
T34
T45
T56
T67

 

REPORT
ID(Desired Result) Order
TR1R1
TR2T1
TR3T2
TR4T3
TR5T1
TR20No

 

Saxon10_1-1627766597095.png

 

2 ACCEPTED SOLUTIONS
parry2k
Super User
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" )

 

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

Mohammad_Refaei
Solution Specialist
Solution Specialist

You can create this relathionship structure

m_refaei_0-1627818823216.png

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:

m_refaei_1-1627818939276.png

 

Please check this sample file.

View solution in original post

8 REPLIES 8
parry2k
Super User
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.

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

Thanks for your quick reply. Can you please advise is there any alternative way to achieve the desired result.

 

DAX new calculate column option. 

Saxon10
Post Prodigy
Post Prodigy

Thanks for your reply @Mohammad_Refaei  and @parry2k.

Both solutions working well. 

Mohammad_Refaei
Solution Specialist
Solution Specialist

You can create this relathionship structure

m_refaei_0-1627818823216.png

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:

m_refaei_1-1627818939276.png

 

Please check this sample file.

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.

Please find the snapshot. 

IMG_20210826_145410.jpg

IMG_20210826_145314.jpg

parry2k
Super User
Super User

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

 

parry2k_0-1627771175257.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

@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" )

 

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.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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