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)
kpost's avatar
kpost
Icon for Solution Sage rankSolution Sage
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///

sstaton6005's avatar
sstaton6005
New Member
1 year ago

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's avatar
    kpost
    Icon for Solution Sage rankSolution Sage
    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)