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 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 column as measure. ID A B C D E Ref1 Ref2 1 200 500 100 300 300 D C 2 200 700 500 300 100 B D 3 200 500 100 300 300 A B 4 200 700 500 300 100 E A Output ID A B C D E Ref1 Ref2 Sum(based on Ref1 & Ref2) 1 200 500 100 300 300 D C 400 2 200 700 500 300 100 B D 1000 3 200 500 100 300 300 A B 700 4 200 700 500 300 100 E A 300

[talespin]

hi @Mani1404 

If you want a complete dynamic solution, I would suggest doing it in power query.

[Anonymous · Accepted solution]

Hi @Mani1404 ,

I created a sample pbix file(see the attachment), please check if that is what you want.

1. Unpivot the data in Power Query Editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TZNBEoMgEAT/4jkHBhDhmERfYeX/34j0LuJByoJ2d3YGz3NRyctriWvsq8K1qpT7/X09n+X3Oi9ig2PdEgRf5ubcF05bmUQ2OrAObjeu9Y4KdKzQAToW5w7vu6KpnykLDYUdoW30jdnm6LteuzBTSs6Nvl2HBLf2M0X01ebc8ZxDpqkxDXQZ3Nv64oJKpRLKKlpDRtvkqKR6a1ejby7Omc+uzFVSOzFNG5zPgcNSmT4nHGCa7/SvrbivxxxUJfH91ic4VTuz3NC6ZudcHzmaZ9rmN5b4PvNQetwX6EAecXCuj3qRO2eEp4eXx/TPaljKEWW53Q4cU1+yu2n5Vu4VO6RyPPyzGuYfnP0JoTp3zfH7Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t, D = _t, E = _t, Ref1 = _t, Ref2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}, {"D", Int64.Type}, {"E", Int64.Type}, {"Ref1", type text}, {"Ref2", type text}}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"E", "D", "C", "B", "A"}, "Type1", "Value"),
    #"Duplicated Column" = Table.DuplicateColumn(#"Unpivoted Only Selected Columns", "Type1", "Type2")
in
    #"Duplicated Column"

2. Create a measure as below to get the sum of value

Measure = 
VAR _ref1 =
    SELECTEDVALUE ( 'Table'[Ref1] )
VAR _ref2 =
    SELECTEDVALUE ( 'Table'[Ref2] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Type1] = _ref1 || 'Table'[Type2] = _ref2 )
    )

If the above one can't help you get the expected result please provide some raw data(exclude the sensitive data) and expected result with specific samples and calculation logic? It would be helpful to get the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[123abc]

It seems like you're trying to calculate a new column in your table based on the values from columns referenced by Ref1 and Ref2. In DAX, you can achieve this by using the SWITCH and RELATED functions to dynamically reference the columns based on the values in Ref1 and Ref2. Here's how you can do it:

Sum(based on Ref1 & Ref2) =
VAR Ref1Value = SELECTEDVALUE('YourTable'[Ref1])
VAR Ref2Value = SELECTEDVALUE('YourTable'[Ref2])

RETURN
SWITCH(TRUE(),
Ref1Value = "A" && Ref2Value = "B", 'YourTable'[A] + 'YourTable'[B],
Ref1Value = "A" && Ref2Value = "C", 'YourTable'[A] + 'YourTable'[C],
Ref1Value = "A" && Ref2Value = "D", 'YourTable'[A] + 'YourTable'[D],
Ref1Value = "A" && Ref2Value = "E", 'YourTable'[A] + 'YourTable'[E],
Ref1Value = "B" && Ref2Value = "A", 'YourTable'[B] + 'YourTable'[A],
Ref1Value = "B" && Ref2Value = "C", 'YourTable'[B] + 'YourTable'[C],
Ref1Value = "B" && Ref2Value = "D", 'YourTable'[B] + 'YourTable'[D],
Ref1Value = "B" && Ref2Value = "E", 'YourTable'[B] + 'YourTable'[E],
Ref1Value = "C" && Ref2Value = "A", 'YourTable'[C] + 'YourTable'[A],
Ref1Value = "C" && Ref2Value = "B", 'YourTable'[C] + 'YourTable'[B],
Ref1Value = "C" && Ref2Value = "D", 'YourTable'[C] + 'YourTable'[D],
Ref1Value = "C" && Ref2Value = "E", 'YourTable'[C] + 'YourTable'[E],
Ref1Value = "D" && Ref2Value = "A", 'YourTable'[D] + 'YourTable'[A],
Ref1Value = "D" && Ref2Value = "B", 'YourTable'[D] + 'YourTable'[B],
Ref1Value = "D" && Ref2Value = "C", 'YourTable'[D] + 'YourTable'[C],
Ref1Value = "D" && Ref2Value = "E", 'YourTable'[D] + 'YourTable'[E],
Ref1Value = "E" && Ref2Value = "A", 'YourTable'[E] + 'YourTable'[A],
Ref1Value = "E" && Ref2Value = "B", 'YourTable'[E] + 'YourTable'[B],
Ref1Value = "E" && Ref2Value = "C", 'YourTable'[E] + 'YourTable'[C],
Ref1Value = "E" && Ref2Value = "D", 'YourTable'[E] + 'YourTable'[D],
0
)

This formula calculates the sum based on the combination of values in Ref1 and Ref2. You need to replace 'YourTable' with the actual name of your table. This formula checks each combination of Ref1 and Ref2 values and sums the corresponding columns. If no match is found, it returns 0.

You can add this DAX measure to your Power BI model or use it in any other DAX-compatible environment to compute the desired sum based on the given conditions.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[Mani1404]

Thanks For your response. you got the requirement right. But the issue is we cannot use Switch statement as the column A to E are dynamic. they may get more columns added F,G,.. so on. Hence i cannot hardcode column names in DAX. It should dymanically match without hardcode

[123abc]

I understand your concern about the dynamic nature of the columns. In that case, we can use a more dynamic approach by iterating through the columns of the table to find the corresponding values for Ref1 and Ref2. We can achieve this using the following DAX measure:

Sum_based_on_Ref1_Ref2 =
VAR Row_Ref1 = SELECTEDVALUE('Table'[Ref1])
VAR Row_Ref2 = SELECTEDVALUE('Table'[Ref2])
VAR SelectedColumns =
SELECTCOLUMNS(
FILTER(
ALLCOLUMNS('Table'),
NOT(ISBLANK('Table'[A])) && NOT(ISBLANK('Table'[B])) && NOT(ISBLANK('Table'[C])) && NOT(ISBLANK('Table'[D])) && NOT(ISBLANK('Table'[E]))
),
"ColumnName", SELECTEDVALUE('Table'[Ref1]),
"ColumnValue", SWITCH(
[ColumnName],
"A", 'Table'[A],
"B", 'Table'[B],
"C", 'Table'[C],
"D", 'Table'[D],
"E", 'Table'[E]
)
)
RETURN
SUMX(
FILTER(
SelectedColumns,
[ColumnName] = Row_Ref1 || [ColumnName] = Row_Ref2
),
[ColumnValue]
)

This measure dynamically selects the columns A to E that are not blank and then iterates through these selected columns to find the corresponding values for Ref1 and Ref2. It then sums these values based on the conditions.

This approach should work dynamically even if you add more columns to your table. Make sure to replace 'Table' with the actual name of your table in your data model.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[Mani1404]

Not able to Use ALLCOLUMNS('Table'), under filter condition.  

[123abc]

If you're encountering issues using ALLCOLUMNS('Table') in your DAX measure, it might be due to the context in which you're trying to use it. ALLCOLUMNS('Table') returns all the columns of the specified table, and it can be used in certain contexts, but there might be limitations or constraints depending on your specific DAX expression.

Here's a general approach to dynamically calculate the sum based on Ref1 and Ref2 without hardcoding column names, using dynamic column references:

Sum based on Ref1 & Ref2 =
VAR Ref1ColumnName =
SELECTEDVALUE ( 'Table'[Ref1] )
VAR Ref2ColumnName =
SELECTEDVALUE ( 'Table'[Ref2] )
VAR Ref1ColumnValue =
SELECTCOLUMNS (
'Table',
Ref1ColumnName,
[Ref1ColumnName]
)
VAR Ref2ColumnValue =
SELECTCOLUMNS (
'Table',
Ref2ColumnName,
[Ref2ColumnName]
)
RETURN
SUMX(ROWS(Ref1ColumnValue), [Ref1ColumnName]) + SUMX(ROWS(Ref2ColumnValue), [Ref2ColumnName])

In this DAX expression:

• Ref1ColumnName and Ref2ColumnName extract the values of Ref1 and Ref2.
• Ref1ColumnValue and Ref2ColumnValue create virtual tables containing only the columns specified by Ref1 and Ref2, respectively, along with their respective column values.
• Finally, the expression sums up the values of Ref1 and Ref2 using SUMX and returns the sum.

This approach dynamically references columns without hardcoding them and should adapt to changes in column names. Make sure to replace 'Table' with the actual name of your table in your data model.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[Mani1404]

Thanks this is good idea of using dynamic column refference, But I just tried the above method, But getting error in Dax. "Parameter is not correct type", while using Ref1ColumnName context in

SELECTCOLUMNS (

'ProfilingFact',

Ref1ColumnName,

[Ref1ColumnName]

).