Forum Discussion

jaydesai28's avatar
jaydesai28
Frequent Visitor
8 years ago
Solved

How to count columns with particular values

Hi,

I am trying to count difference coulms with 0 value in it. 

output column name is Count 0.

Count 0 = COUNTIF([#"A - B"] = 0,[#"C - D"] = 0,[#"E - F"] = 0)

It doen't work. Please suugest some function.

  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi jaydesai28,

     

    You may try to achieve this requirement via Power Query.

    let
    Source = Excel.Workbook(File.Contents("C:\Users\xxxx\Desktop\Sample Data.xlsx"), null, true),
    #"Test data_Sheet" = Source{[Item="Test data",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"Test data_Sheet", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Id", Int64.Type}, {"A", Int64.Type}, {"B", Int64.Type}, {"A-B", Int64.Type}, {"C", Int64.Type}, {"D", Int64.Type}, {"C-D", Int64.Type}, {"E", Int64.Type}, {"F", Int64.Type}, {"E-F", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Id", "A", "B", "C", "D", "E", "F"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Custom", each if [Value]=0 then 1 else 0),
    #"Added Custom2" = Table.AddColumn(#"Added Custom", "TotalCount", (This) => List.Sum(Table.SelectRows(#"Added Custom",each [Id] = This[Id])[Custom])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Custom"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "Value", List.Sum)
in
    #"Pivoted Column"

    I have uploaded the test .pbix file for your reference. Please check the "Applied Steps" in Query Editor mode.

     

    Best regards,

    Yuliana Gu

6 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi jaydesai28

     

    If your DAX table looks exactly like the image and your column names are [A - B] etc, then you can try

     

    Measure = CALCULATE(
            COUNTROWS('Table'),
            ALLSELECTED('Table'),
            'Table'[A - B] = 0, 
            'Table'[C - D] = 0 , 
            'Table'[E - F] = 0
            )
      • Phil_Seamark's avatar
        Phil_Seamark
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        Hi jaydesai28

         

        The code I suggested was for a calculated measure.

         

        If you'd like it as a calculated column then please try this

         

        Column= if(
            
            'Table'[A - B] = 0 &&
            'Table'[C - D] = 0 &&
            'Table'[E - F] = 0 , 1, ,0
            )