Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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
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 )
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 )
It's only checking whether coulums have 0 or not. I have to count the zeros.
The ouput should be like as below:
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
Thanks for contributing to this post. I tried this strategy for a similar data set and was successful with the counting procedure, but I am getting duplicates in my data set after the Pivot step. I believe the List.Distinct fuction is supposed to take care of that, so I must have something entered wrong. My code is below, do you have any insight into how I can fix this? I removed columns names from the first few steps for brevity.
let Source = Excel.Workbook(Web.Contents(Path & File, [Timeout=#duration(0, 0, 2, 0)]), null, true), Worksheet2 = Source{[Name="Worksheet1"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Worksheet2, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers"), #"Renamed Columns" = Table.RenameColumns(#"Changed Type"), #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns"), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Name"}, "Attribute", "Value"), #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "L1Yes", each if [Value] = "Yes" and Text.Contains([Attribute], "L1") then 1 else 0 ), #"Added Custom2" = Table.AddColumn(#"Added Custom", "L1No", each if [Value] = "No" and Text.Contains([Attribute], "L1") then 1 else 0 ), #"Added Custom3" = Table.AddColumn(#"Added Custom2", "L1NA", each if [Value] = "N/A" and Text.Contains([Attribute], "L1") then 1 else 0 ), #"Added Custom4" = Table.AddColumn(#"Added Custom3", "CountL1Yes", (This) => List.Sum(Table.SelectRows(#"Added Custom3",each [Name] = This[Name])[L1Yes])), #"Added Custom5" = Table.AddColumn(#"Added Custom4", "CountL1No", (This) => List.Sum(Table.SelectRows(#"Added Custom4",each [Name] = This[Name])[L1No])), #"Added Custom6" = Table.AddColumn(#"Added Custom5", "CountL1NA", (This) => List.Sum(Table.SelectRows(#"Added Custom5",each [Name] = This[Name])[L1NA])), #"Pivoted Column" = Table.Pivot(#Added Custom6", List.Distinct(#"Removed Columns2"[Attribute]), "Attribute", "Value", List.Sum), #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"L1.1", "L2.1", "L2.2", "L2.3", "L2.4", "L2.5", "L2.6", "L2.7", "L2.8", "L2.9", "L2.10", "L2.11", "L3.1", "L3.2", "L3.3", "L3.4", "L3.5", "L3.6", "L3.7", "L3.8", "L3.9", "L3.10", "L3.11", "L4.1"}), #"Added Custom1" = Table.AddColumn(#"Removed Columns1", "L1PercentComplete", each [CountL1Yes]/([CountL1Yes]+[CountL1No])), in #"Added Custom1"
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |