Need Help on DAX by passing row value and match with column and get respective row value

[Mani1404]

I have below table A-E & Ref1 & Ref2. Now i will need to calculate the Sum for each row based on column Ref1 & Ref2. it should take the value of Ref1 and match with columns from A-D, suppose D is value of Ref1 & C for ref2 then it should take from column C & D and give Sum of 100 + 300 = 400 in new calculated measure. I cannot hardcode the column names  using Switch in DAX as they are dynamic and can change. 

Table1

Output

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but please check the attached pbix file.

expected result CC = 
VAR _a =
    ADDCOLUMNS (
        VALUES ( 'Data'[ID] ),
        "A", "A",
        "@value", CALCULATE ( SUM ( 'Data'[A] ) )
    )
VAR _b =
    ADDCOLUMNS (
        VALUES ( 'Data'[ID] ),
        "A", "B",
        "@value", CALCULATE ( SUM ( 'Data'[B] ) )
    )
VAR _c =
    ADDCOLUMNS (
        VALUES ( 'Data'[ID] ),
        "A", "C",
        "@value", CALCULATE ( SUM ( 'Data'[C] ) )
    )
VAR _d =
    ADDCOLUMNS (
        VALUES ( 'Data'[ID] ),
        "A", "D",
        "@value", CALCULATE ( SUM ( 'Data'[D] ) )
    )
VAR _e =
    ADDCOLUMNS (
        VALUES ( 'Data'[ID] ),
        "A", "E",
        "@value", CALCULATE ( SUM ( 'Data'[E] ) )
    )
VAR _t =
    SELECTCOLUMNS (
        UNION ( _a, _b, _c, _d, _e ),
        "ID", 'Data'[ID],
        "Ref", [A],
        "@value", [@value]
    )
RETURN
    MAXX (
        FILTER (
            ADDCOLUMNS (
                'Data',
                "@sum",
                    SUMX (
                        FILTER (
                            _t,
                            'Data'[ID] = EARLIER ( 'Data'[ID] )
                                && [Ref] = EARLIER ( 'Data'[Ref1] )
                        ),
                        [@value]
                    )
                        + SUMX (
                            FILTER (
                                _t,
                                'Data'[ID] = EARLIER ( 'Data'[ID] )
                                    && [Ref] = EARLIER ( 'Data'[Ref2] )
                            ),
                            [@value]
                        )
            ),
            Data[ID] = EARLIER ( Data[ID] )
        ),
        [@sum]
    )

[Mani1404]

Thanks for your response. But the Issue is i cannot hardcode my query. it should do sometime like Hlookup funtionalitiy. take value D from ref1 Column and match with Column header A - E, and get D columns's row value. the issue is i cannot hardcode my values becuase column A-E may increase to A-Z...