How to SumIf with multiple columns in DAX

[sstaton6005]

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

[kpost · Accepted solution]

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///

[v-tsaipranay]

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.

[v-tsaipranay]

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.

[v-tsaipranay]

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.

[rajendraongole1]

Hi @sstaton6005 - please find the attached pbix with solution.

Hope this helps.

[sstaton6005]

I'm sorry, I"m not sure what the DAX query is that you used or how you did that. 

[kpost · Accepted solution]

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///

[sstaton6005]

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 · Accepted solution]

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)