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

[Mani1404]

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 · Accepted solution]

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.