Forum Discussion
Solved
How to SumIf with multiple columns in DAX
I am hitting a wall and cannot figure out how to return a result where I could in Excel. My data shows whether steps in a process have been completed. A violation would be if the Status is compl...
First way: Measure using DAX
violation_count =COALESCE(SUMX('Table_Name',IF([Status] = "Completed" && ([A] <> "done" || [B] <> "done" || [C] <> "done"), 1, 0)),0)
Second way, custom column using M in Power Query:if ([Status] = "Completed") and ( [A] <> "done" or [B] <> "done" or [C] <> "done" ) then 1 else 0
Then sum this column in the Power BI report.
You will have to change where it says "A", "B", "C" etc. with the names of your columns.
See attached .pbix file with the solution(s).
There's probably a few ways to do this but....
///Mediocre Power BI advice, but it's free///
It's not DAX, I did it in Power Query using the "M" language.
Here's the same thing as a custom column using DAX language, you'll still have to sum it:
Violation_DAX_COLUMN =IF([Status] = "Completed" && ([A] <> "done" || [B] <> "done" || [C] <> "done"), 1, 0)And here's the same thing using a single measure rather than creating a custom column and summing it:
violation_count_MEASURE =COALESCE(SUMX('Table_Name',IF([Status] = "Completed" && ([A] <> "done" || [B] <> "done" || [C] <> "done"), 1, 0)),0)
rajendraongole1
Super User
Super User
I'm sorry, I"m not sure what the DAX query is that you used or how you did that.