Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I'm trying to find a way to SUM a column if another "column A" in the same table has a specific value OR "column B" in the same table has a specific value.
My workaround was to CONCATENATE column A and column B and use the value in the concatenated column in my SUM measure. However, and correct me if I'm wrong, I don't think creating custom columns is optimal for performance when working with large data sets.
Hope the above describes the question well. Thanks for looking into this!
Kind regards,
Ivan
Solved! Go to Solution.
Hi, @Anonymous
If I understand you correctly. You are able to do that with CONTAINS function:
Measure =
CALCULATE (
SUM ( 'Table'[Column] ),
FILTER (
'Table',
CONTAINS ( 'Table', 'Table'[ColumnA], "Text1" )
|| CONTAINS ( 'Table', 'Table'[ColumnB], "Text2" )
)
)
Paul
Best
Hi again @V-pazhen-msft ,
Just found the solution. What worked for me is the below. I had to get rid of the "CONTAINS" which actually works better for me as I have exact values. Have to describe my requests better in future forum posts 🙂 .
Hi, @Anonymous
If I understand you correctly. You are able to do that with CONTAINS function:
Measure =
CALCULATE (
SUM ( 'Table'[Column] ),
FILTER (
'Table',
CONTAINS ( 'Table', 'Table'[ColumnA], "Text1" )
|| CONTAINS ( 'Table', 'Table'[ColumnB], "Text2" )
)
)
Paul
Best
Hi again @V-pazhen-msft ,
Just found the solution. What worked for me is the below. I had to get rid of the "CONTAINS" which actually works better for me as I have exact values. Have to describe my requests better in future forum posts 🙂 .
Hi @V-pazhen-msft ,
Thanks for sharing the solution, what if I want the same calculation to happen when two columns contain a specific value simultaneously? E.g. calculate only when column 1 has "x" AND column 2 has "y"? I tried replacing || with && but haven't achieved the desired result.
Appreciate your help.
Ivan
Share the formula you are using as of now.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
99 | |
88 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |