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 ...
  • 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

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
            )
  • jaydesai28's avatar
    jaydesai28
    Frequent Visitor
    8 years ago

    Hi Phil_Seamark

     

    I am getting blank values for new column.

     

    • 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
            )
      • jaydesai28's avatar
        jaydesai28
        Frequent Visitor
        8 years ago

        Hi Phil_Seamark

         

        It's only checking whether coulums have 0 or not. I have to count the zeros.

         

         

        The ouput should be like as below: