topic Re: Count True/False values in multiple columns in DAX Commands and Tips

Count True/False values in multiple columns

JMM414141 — Thu, 09 Jul 2020 15:38:11 GMT

I have a table with about 50 columns with rows that either have True or False in them. Is it possible to count the number of times either true or false appears in each column without going through each column one by one?

Re: Count True/False values in multiple columns

AntrikshSharma — Thu, 09 Jul 2020 15:51:13 GMT

Why not unpivot the data?

Re: Count True/False values in multiple columns

camargos88 — Thu, 09 Jul 2020 16:33:44 GMT

Hi JMM414141 ,

Can you show some example ?

Re: Count True/False values in multiple columns

JMM414141 — Thu, 09 Jul 2020 16:56:16 GMT

A	B	C
True	False	True
False	False	True

	A	B	C
True	1	0	2
False	1	2	0

So I have something like the first table and im trying to get something like the 2nd table where it shows the number of true/false that exist in each column.

Re: Count True/False values in multiple columns

camargos88 — Thu, 09 Jul 2020 17:25:49 GMT

Hi JMM414141 ,

This is a Power Query solution, create a blank query and past this m code:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCikqTVXSUXJLzCkG0WBurE40XABVIhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type logical}, {"B", type logical}, {"C", type logical}}),
Table = Table.Combine({ Table.AddColumn(#"Changed Type", "Type", each "True"), Table.AddColumn(#"Changed Type", "Type", each "False") }),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Table, {"Type"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each if Logical.FromText([Type]) = [Value] then 1 else 0),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "Custom", List.Sum),
#"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",{{"Type", type logical}, {"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}})
in
#"Changed Type1"