Forum Discussion
Solved
Lookup related record in same table
Product table
Sku | ID | Price |
Prod1_a | Prod1 | 5 |
| Prod1_b | Prod1 | 6 |
| Prod1_c | Prod1 | 7 |
| Prod2_a | Prod2 | 7 |
| Prod2_b | Prod2 | 6 |
What dax measure can be used to return the price of the MAX(Sku) for each ID?
i.e All Prod1 would show 7 and all Prod2 would show 6.
Thank you
EmJa you can use a measure like this
Measure = VAR _id = MAX ( 'Table 1'[ID] ) VAR _sku = CALCULATE ( MAX ( 'Table 1'[Sku] ), FILTER ( ALL ( 'Table 1' ), 'Table 1'[ID] = _id && RIGHT ( 'Table 1'[Sku], 1 ) = CALCULATE ( RIGHT ( MAX ( 'Table 1'[Sku] ), 1 ), ALLEXCEPT ( 'Table 1', 'Table 1'[ID] ) ) ) ) RETURN CALCULATE ( MAX ( 'Table 1'[Price] ), FILTER ( ALL ( 'Table 1' ), 'Table 1'[ID] = _id && 'Table 1'[Sku] = _sku ) )
2 Replies
smpa01Community Champion
EmJa you can use a measure like this
Measure = VAR _id = MAX ( 'Table 1'[ID] ) VAR _sku = CALCULATE ( MAX ( 'Table 1'[Sku] ), FILTER ( ALL ( 'Table 1' ), 'Table 1'[ID] = _id && RIGHT ( 'Table 1'[Sku], 1 ) = CALCULATE ( RIGHT ( MAX ( 'Table 1'[Sku] ), 1 ), ALLEXCEPT ( 'Table 1', 'Table 1'[ID] ) ) ) ) RETURN CALCULATE ( MAX ( 'Table 1'[Price] ), FILTER ( ALL ( 'Table 1' ), 'Table 1'[ID] = _id && 'Table 1'[Sku] = _sku ) )
