Forum Discussion
Post Prodigylookup 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 |
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.⚡
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:
Please check this sample file.
8 Replies
- parry2k
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.⚡
- Mohammad_Refaei
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:
Please check this sample file.
Saxon10Hi,
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.
- Saxon10
Thanks for your reply Mohammad_Refaei and parry2k.
Both solutions working well.
- Saxon10
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
I am happy to maintain additional columns in my data, report or priority table. Could you please advise is there any alternative solutions.