Forum Discussion

sstaton6005's avatar
sstaton6005
New Member
1 year ago
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...
Need Help
  • kpost's avatar
    kpost
    1 year ago

     

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

  • kpost's avatar
    kpost
    1 year ago

    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)
Anonymous's avatar
Anonymous
Not applicable
1 year ago

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.