Forum Discussion

EmJa's avatar
EmJa
Frequent Visitor
4 years ago
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 pric...
Need Help
  • smpa01's avatar
    smpa01
    4 years ago

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

     

     

     

smpa01's avatar
smpa01
Icon for Community Champion rankCommunity Champion
4 years ago

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

 

 

 

EmJa's avatar
EmJa
Frequent Visitor
4 years ago

Thank you smpa01 , not just for the solution but for th concept. 

(That was a really quick response!)