Forum Discussion
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 complete, but not all steps were completed. I want to be able to show violations. In the example below, row 3 & 4 would be a violation. I need to count how many violations I have.
| Pre work | required docs | equipment check | final run through | approval to proceed | Status |
| done | done | pending | done | done | Planning |
| done | done | done | done | done | Completed |
| done | done | pending | pending | done | Completed |
| pending | done | done | done | done | Completed |
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)
8 Replies
kpostSolution Sage
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///
Thanks for trying! It keeps saying that the syntax for AND is incorrect. I've tried copying and pasting your suggestion as well as retyping my own.
- kpost
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
I'm sorry, I"m not sure what the DAX query is that you used or how you did that.
Hi sstaton6005 ,
Thanks for reaching out to the Microsoft fabric community forum.
I wanted to check if you had the opportunity to review the information provided by kpost . Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.Hi sstaton6005 ,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
Hi @sstaton6005 ,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
