Forum Discussion

Mani1404's avatar
Mani1404
Regular Visitor
2 years ago
Solved

Finding value based on Column header and a column value and get respective Row Value

I have below two tables Cost & Order with JoinConcatColumn as common column with 1 to many Relationship. Now in Order tables i will like to see the Numerator, Denominatior and Sum columns value based on lookup to the coulmn names in Cost table.

 

For Ex: JoinConcatColumn is 128 in Order table, and for 128 in Cost table the for Numerator column the value is Attr2Value. I will then take this value and match with column headers Attr1Value, Attr2Value, Attr3Value, Attr4Value, Attr5Value and then  get Attr5Value's value in new Numerator as measure. 

 

Please Note: issue is cannot use   Attr1Value, Attr2Value, Attr3Value, Attr4Value, Attr5Value as hardcode because attribue column keeps added.  Attr6Value, Attr7Value.....so on

 

 For: in Ordertable 

 

 

Output:

 

 

 

 

 

  • v-yanimei-msft's avatar
    v-yanimei-msft
    2 years ago

    Hi Mani1404 ,

    If Attr1Value, Attr2Value, Attr3Value, Attr4Value, Attr5Value cannot be used as hardcode, the outcome cannot be realized. That's the best DAX can do.

     

     

    Numerator1 =
VAR A = 'Order'[JoinConcatColumn]
VAR B =
CALCULATE(
    MAX('Cost'[Numerator]),
    FILTER(
        'Cost',
        'Cost'[JoinConcatColumn] = A
    )
)
RETURN
B
 
Denominator1 =
VAR A = 'Order'[JoinConcatColumn]
VAR B =
CALCULATE(
    MAX('Cost'[Denominator]),
    FILTER(
        'Cost',
        'Cost'[JoinConcatColumn] = A
    )
)
RETURN
B

     

     

    The final output is like this:

    If you are willing to use them as hardcode, it may be complex.

    Please follow these steps:

      1.Right-click Order, select New Column and input:

     

     

    Numerator = 
    SWITCH(
        LOOKUPVALUE(Cost[Numerator],Cost[JoinConcatColumn],'Order'[JoinConcatColumn]),
        "Attr1Value",'Order'[Attr1Value],
        "Attr2Value",'Order'[Attr2Value],
        "Attr3Value",'Order'[Attr3Value],
        "Attr4Value",'Order'[Attr4Value],
        "Attr5Value",'Order'[Attr5Value])

     

     

    2.Right-click Order, select New Column and input:

     

     

    Denominator = 
SWITCH(
    LOOKUPVALUE(Cost[Denominator],Cost[JoinConcatColumn],'Order'[JoinConcatColumn]),
    "Attr1Value",'Order'[Attr1Value],
    "Attr2Value",'Order'[Attr2Value],
    "Attr3Value",'Order'[Attr3Value],
    "Attr4Value",'Order'[Attr4Value],
    "Attr5Value",'Order'[Attr5Value]
    )

     

     

    3.Right-click Order, select New Column and input:

     

     

    Sum = 
SWITCH(
    LOOKUPVALUE(Cost[Sum],Cost[JoinConcatColumn],'Order'[JoinConcatColumn]),
    "Attr1Value",'Order'[Attr1Value],
    "Attr2Value",'Order'[Attr2Value],
    "Attr3Value",'Order'[Attr3Value],
    "Attr4Value",'Order'[Attr4Value],
    "Attr5Value",'Order'[Attr5Value])

     

     

    Best Regards,
    Caroline Mei
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • v-yanimei-msft's avatar
    v-yanimei-msft
    Icon for Community Support rankCommunity Support
    2 years ago

    Hi Mani1404 ,

    If Attr1Value, Attr2Value, Attr3Value, Attr4Value, Attr5Value cannot be used as hardcode, the outcome cannot be realized. That's the best DAX can do.

     

     

    Numerator1 =
VAR A = 'Order'[JoinConcatColumn]
VAR B =
CALCULATE(
    MAX('Cost'[Numerator]),
    FILTER(
        'Cost',
        'Cost'[JoinConcatColumn] = A
    )
)
RETURN
B
 
Denominator1 =
VAR A = 'Order'[JoinConcatColumn]
VAR B =
CALCULATE(
    MAX('Cost'[Denominator]),
    FILTER(
        'Cost',
        'Cost'[JoinConcatColumn] = A
    )
)
RETURN
B

     

     

    The final output is like this:

    If you are willing to use them as hardcode, it may be complex.

    Please follow these steps:

      1.Right-click Order, select New Column and input:

     

     

    Numerator = 
    SWITCH(
        LOOKUPVALUE(Cost[Numerator],Cost[JoinConcatColumn],'Order'[JoinConcatColumn]),
        "Attr1Value",'Order'[Attr1Value],
        "Attr2Value",'Order'[Attr2Value],
        "Attr3Value",'Order'[Attr3Value],
        "Attr4Value",'Order'[Attr4Value],
        "Attr5Value",'Order'[Attr5Value])

     

     

    2.Right-click Order, select New Column and input:

     

     

    Denominator = 
SWITCH(
    LOOKUPVALUE(Cost[Denominator],Cost[JoinConcatColumn],'Order'[JoinConcatColumn]),
    "Attr1Value",'Order'[Attr1Value],
    "Attr2Value",'Order'[Attr2Value],
    "Attr3Value",'Order'[Attr3Value],
    "Attr4Value",'Order'[Attr4Value],
    "Attr5Value",'Order'[Attr5Value]
    )

     

     

    3.Right-click Order, select New Column and input:

     

     

    Sum = 
SWITCH(
    LOOKUPVALUE(Cost[Sum],Cost[JoinConcatColumn],'Order'[JoinConcatColumn]),
    "Attr1Value",'Order'[Attr1Value],
    "Attr2Value",'Order'[Attr2Value],
    "Attr3Value",'Order'[Attr3Value],
    "Attr4Value",'Order'[Attr4Value],
    "Attr5Value",'Order'[Attr5Value])

     

     

    Best Regards,
    Caroline Mei
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.