Forum Discussion
How to count columns with particular values
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
Microsoft EmployeeHi 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
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 )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:
v-yulgu-msftHi 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