lookup ascending order

[Saxon10]

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

[parry2k · Accepted solution]

@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.⚡

[parry2k]

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

[Saxon10]

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

[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]

Thanks for your reply @Mohammad_Refaei  and @parry2k.

Both solutions working well. 

[Mohammad_Refaei · Accepted solution]

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.

[Saxon10]

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. 

 

 

[parry2k]

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

[parry2k · Accepted solution]

@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.⚡