Lookup using measure and dax from one table to another table

[Saxon10]

I have 3 tables are Data, Report and Priority.

In Data table contains duplicate items with unique code and qty, report table contain unique item only and Priority table contain order for the code.

In data table the same item has two different codes are "CHE" and "RCB" so I would like to pull the code and qty against code "CHE" only that's my first priority and there is no "CHE" then pick from "RCB" that's secound priority.

In data table the items has duplicate with code so I created two different columns in data table (count, Unique count) to get the unique count for code against the items.

CODE ORDER =

VAR CODE_ORDER = DATA[CODE]

RETURN

LOOKUPVALUE(PRIORITY[ORDER],PRIORITY[CODE],CODE_ORDER)

CODE ORDER UNIQUE COUNT =

VAR CODE_ORDER = DATA[CODE ORDER]

RETURN

IF(CODE_ORDER = CALCULATE(MIN(DATA[CODE ORDER]),ALLEXCEPT(DATA,DATA[ITEM])),1,0)

and finally I used lookupvalue function to bring the code and qty against the item in report table by using below mentioned formula

CODE = LOOKUPVALUE(DATA[CODE],DATA[ITEM],REPORT[ITEM],DATA[CODE ORDER UNIQUE COUNT],1)

QTY = LOOKUPVALUE(DATA[QTY],DATA[ITEM],REPORT[ITEM],DATA[CODE ORDER UNIQUE COUNT],1)

Question:

1. I want to get the same result by using DAX New calculated column option without duplication in data table (Count and unique count column in data table- Creating DAX from data table into report table without priority table).

2. I want to get the same result by using Measure option without duplication in data table (Count and unique count column in data table- Creating Measure from data table into report table without priority table).

Link here for PBI file https://www.dropbox.com/s/9xoyhnqthp5td3y/LOOKUPVALUR-MES%26CAL-28-07-22.pbix?dl=0 

[lbendlin]

Power BI likes data models in star or snowflake format.  Dimensions on the outside, facts in the middle.  Your tables are a mix of facts and dimensions. I would recommend you rethink and refactor your data model to separate dimensions from facts. 

LOOKUPVALUE  is something you use between unrelated tables. In a good data model you don't need to use that, let the data model do the work for you. Worst case use TREATAS.

[Saxon10]

@lbendlin,

Thanks for your reply. 

Can I get a measure for similar to the calculate column logic. I am trying to bring it column in measure but its supporting. 

That's the raw data so its difficult to change the date but I try it. 

[lbendlin]

attached is a cleaned up version. It does not require any LOOKUPVALUE.  Technically the code order table is not required either as the codes are already sorted alphabetically.

[Saxon10]

@lbendlin,

Thanks for your reply and solution and I am aware of the options. 

I want the same result within the same same column by using DAX code(New calculate column and measure) without duplication column in data table. 

I am currently achieving the result by using lookvalue function with adding the support columns in data table. I am trying to avoid the option and looking for alternative solutions. 

Thank you. 

[lbendlin]

"I want the same result within the same same column "

Can't help you with that. I think it's not a good design. Maybe someone else can chime in.