Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi, I am not sure this can be done in PowerBI DAX but trying to find some countif equivalent from Excel like below (Range 10, range20 and range30)
col1 | col2 | col3 | col4 | col5 | col6 | col7 | col8 | col9 | col10 | range10 | range20 | range30 |
35 | 91 | 12 | 57 | 90 | 60 | 7 | 10 | 29 | 27 | COUNTIF(A2:J2,"<"&11) | COUNTIFS(A2:J2,">"&10,A2:J2,"<"&21) | COUNTIFS(A2:J2,">"&20,A2:J2,"<"&31) |
100 | 17 | 78 | 12 | 3 | 79 | 56 | 11 | 6 | 58 | COUNTIF(A3:J3,"<"&11) | COUNTIFS(A3:J3,">"&10,A3:J3,"<"&21) | COUNTIFS(A3:J3,">"&20,A3:J3,"<"&31) |
50 | 58 | 50 | 20 | 71 | 34 | 95 | 25 | 68 | 94 | COUNTIF(A4:J4,"<"&11) | COUNTIFS(A4:J4,">"&10,A4:J4,"<"&21) | COUNTIFS(A4:J4,">"&20,A4:J4,"<"&31) |
98 | 32 | 67 | 15 | 39 | 67 | 21 | 2 | 18 | 41 | COUNTIF(A5:J5,"<"&11) | COUNTIFS(A5:J5,">"&10,A5:J5,"<"&21) | COUNTIFS(A5:J5,">"&20,A5:J5,"<"&31) |
76 | 33 | 15 | 49 | 80 | 62 | 38 | 44 | 22 | 94 | COUNTIF(A6:J6,"<"&11) | COUNTIFS(A6:J6,">"&10,A6:J6,"<"&21) | COUNTIFS(A6:J6,">"&20,A6:J6,"<"&31) |
13 | 33 | 48 | 28 | 9 | 11 | 47 | 84 | 16 | 29 | COUNTIF(A7:J7,"<"&11) | COUNTIFS(A7:J7,">"&10,A7:J7,"<"&21) | COUNTIFS(A7:J7,">"&20,A7:J7,"<"&31) |
eventually the 3 columns will have following results:
range10 | range20 | range30 |
2 | 1 | 2 |
2 | 3 | 0 |
0 | 1 | 1 |
1 | 2 | 1 |
0 | 1 | 1 |
1 | 3 | 2 |
can this be done?
Solved! Go to Solution.
Hi @theo ,
I created a sample pbix file(see attachment), please check whether that is what you want.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RZDJDUQhDEN74czhZ2OpBdF/G8MLQnOxnMSOA2sVi1LLlAOiB6JTfgcaQCUQnUAvu67ToCPM+nhGo0IUjRYLITHSEt/l9TLN3WjMyeMGBRqS6emZcGN3yzOY23yl4mYoyFzS0ok0e2pHPfIxeWIqCVT9x4g9jzPXvOA9wYkaWKTdX9j7Bw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [col1 = _t, col2 = _t, col3 = _t, col4 = _t, col5 = _t, col6 = _t, col7 = _t, col8 = _t, col9 = _t, col10 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"col1", Int64.Type}, {"col2", Int64.Type}, {"col3", Int64.Type}, {"col4", Int64.Type}, {"col5", Int64.Type}, {"col6", Int64.Type}, {"col7", Int64.Type}, {"col8", Int64.Type}, {"col9", Int64.Type}, {"col10", Int64.Type}}),
MyCols = List.Buffer(List.Select(Table.ColumnNames(#"Changed Type"), each Text.StartsWith(_, "col"))),
#"Added Custom" = Table.AddColumn(#"Changed Type" , "Range10", each List.Count(List.Select(Record.ToList(Record.SelectFields(_,MyCols)), each _<11)) ,Int64.Type),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Range20", each List.Count(List.Select(Record.ToList(Record.SelectFields(_,MyCols)), each _<21 and _>=11))),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Range30", each List.Count(List.Select(Record.ToList(Record.SelectFields(_,MyCols)), each _<31 and _>=20)))
in
#"Added Custom2"
Of course, you can also follow the suggestion by @Greg_Deckler , add index column in Power Query Editor and unpivot these value columns. Finally, create 3 measures to get the counts...
Note: This method will destroy the original structure of the table...
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RZDJDUQhDEN74czhZ2OpBdF/G8MLQnOxnMSOA2sVi1LLlAOiB6JTfgcaQCUQnUAvu67ToCPM+nhGo0IUjRYLITHSEt/l9TLN3WjMyeMGBRqS6emZcGN3yzOY23yl4mYoyFzS0ok0e2pHPfIxeWIqCVT9x4g9jzPXvOA9wYkaWKTdX9j7Bw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [col1 = _t, col2 = _t, col3 = _t, col4 = _t, col5 = _t, col6 = _t, col7 = _t, col8 = _t, col9 = _t, col10 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"col1", Int64.Type}, {"col2", Int64.Type}, {"col3", Int64.Type}, {"col4", Int64.Type}, {"col5", Int64.Type}, {"col6", Int64.Type}, {"col7", Int64.Type}, {"col8", Int64.Type}, {"col9", Int64.Type}, {"col10", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each List.Count(List.Select( #"Added Index"[Index], each _<11))),
#"Unpivoted Only Selected Columns" = Table.Unpivot(#"Added Custom", {"col10", "col9", "col8", "col7", "col6", "col5", "col4", "col3", "col2", "col1"}, "Cols", "Value")
in
#"Unpivoted Only Selected Columns"
Range10_1 = CALCULATE(COUNT('7Jun_output (2)'[Cols]),FILTER('7Jun_output (2)','7Jun_output (2)'[Index]=SELECTEDVALUE('7Jun_output (2)'[Index])&&'7Jun_output (2)'[Value]<11))
Range20_1 = CALCULATE(COUNT('7Jun_output (2)'[Cols]),FILTER('7Jun_output (2)','7Jun_output (2)'[Index]=SELECTEDVALUE('7Jun_output (2)'[Index])&&'7Jun_output (2)'[Value]<21))
Range30_1 = CALCULATE(COUNT('7Jun_output (2)'[Cols]),FILTER('7Jun_output (2)','7Jun_output (2)'[Index]=SELECTEDVALUE('7Jun_output (2)'[Index])&&'7Jun_output (2)'[Value]<31))
Best Regards
Hi @theo ,
I created a sample pbix file(see attachment), please check whether that is what you want.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RZDJDUQhDEN74czhZ2OpBdF/G8MLQnOxnMSOA2sVi1LLlAOiB6JTfgcaQCUQnUAvu67ToCPM+nhGo0IUjRYLITHSEt/l9TLN3WjMyeMGBRqS6emZcGN3yzOY23yl4mYoyFzS0ok0e2pHPfIxeWIqCVT9x4g9jzPXvOA9wYkaWKTdX9j7Bw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [col1 = _t, col2 = _t, col3 = _t, col4 = _t, col5 = _t, col6 = _t, col7 = _t, col8 = _t, col9 = _t, col10 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"col1", Int64.Type}, {"col2", Int64.Type}, {"col3", Int64.Type}, {"col4", Int64.Type}, {"col5", Int64.Type}, {"col6", Int64.Type}, {"col7", Int64.Type}, {"col8", Int64.Type}, {"col9", Int64.Type}, {"col10", Int64.Type}}),
MyCols = List.Buffer(List.Select(Table.ColumnNames(#"Changed Type"), each Text.StartsWith(_, "col"))),
#"Added Custom" = Table.AddColumn(#"Changed Type" , "Range10", each List.Count(List.Select(Record.ToList(Record.SelectFields(_,MyCols)), each _<11)) ,Int64.Type),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Range20", each List.Count(List.Select(Record.ToList(Record.SelectFields(_,MyCols)), each _<21 and _>=11))),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Range30", each List.Count(List.Select(Record.ToList(Record.SelectFields(_,MyCols)), each _<31 and _>=20)))
in
#"Added Custom2"
Of course, you can also follow the suggestion by @Greg_Deckler , add index column in Power Query Editor and unpivot these value columns. Finally, create 3 measures to get the counts...
Note: This method will destroy the original structure of the table...
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RZDJDUQhDEN74czhZ2OpBdF/G8MLQnOxnMSOA2sVi1LLlAOiB6JTfgcaQCUQnUAvu67ToCPM+nhGo0IUjRYLITHSEt/l9TLN3WjMyeMGBRqS6emZcGN3yzOY23yl4mYoyFzS0ok0e2pHPfIxeWIqCVT9x4g9jzPXvOA9wYkaWKTdX9j7Bw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [col1 = _t, col2 = _t, col3 = _t, col4 = _t, col5 = _t, col6 = _t, col7 = _t, col8 = _t, col9 = _t, col10 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"col1", Int64.Type}, {"col2", Int64.Type}, {"col3", Int64.Type}, {"col4", Int64.Type}, {"col5", Int64.Type}, {"col6", Int64.Type}, {"col7", Int64.Type}, {"col8", Int64.Type}, {"col9", Int64.Type}, {"col10", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each List.Count(List.Select( #"Added Index"[Index], each _<11))),
#"Unpivoted Only Selected Columns" = Table.Unpivot(#"Added Custom", {"col10", "col9", "col8", "col7", "col6", "col5", "col4", "col3", "col2", "col1"}, "Cols", "Value")
in
#"Unpivoted Only Selected Columns"
Range10_1 = CALCULATE(COUNT('7Jun_output (2)'[Cols]),FILTER('7Jun_output (2)','7Jun_output (2)'[Index]=SELECTEDVALUE('7Jun_output (2)'[Index])&&'7Jun_output (2)'[Value]<11))
Range20_1 = CALCULATE(COUNT('7Jun_output (2)'[Cols]),FILTER('7Jun_output (2)','7Jun_output (2)'[Index]=SELECTEDVALUE('7Jun_output (2)'[Index])&&'7Jun_output (2)'[Value]<21))
Range30_1 = CALCULATE(COUNT('7Jun_output (2)'[Cols]),FILTER('7Jun_output (2)','7Jun_output (2)'[Index]=SELECTEDVALUE('7Jun_output (2)'[Index])&&'7Jun_output (2)'[Value]<31))
Best Regards
@theo COUNTIF equivalent can be found here: Excel to DAX Translation - Microsoft Power BI Community
COUNTX(FILTER(...)...) or CALCULATE(COUNT(),FILTER(...)) |
For example, if you wanted range0 as a column in your table:
range10 =
COUNTX(FILTER({ [col1], [col2], [col3], [col4], [col5], [col6], [col7], [col8], [col9], [col10] },[Value]<11),[Value])+0
@Greg_Deckler thanks for sharing your post. It is very helpful, however, when I try it to filter 2 col for now, the measure is blank. Anything I missed?
@theo Assuming you have some type of Index column or other identifier for your rows, you can do this:
range10 measure =
VAR __Table = UNION( { MAX([col1]) }, { MAX([col2]) }, { MAX([col3]) }, { MAX([col4]) }, { MAX([col5]) }, { MAX([col6]) }, { MAX([col7]) }, { MAX([col8]) }, { MAX([col9]) }, { MAX([col10]) } )
RETURN
COUNTX(FILTER(__Table,[Value]<11),[Value])+0
That said, are you sure you don't want to unpivot your columns? Makes things soooo much easier. Otherwise it is the MC Aggregations pattern. Multi-Column Aggregations (MC Aggregations) - Microsoft Power BI Community
@Greg_Deckler can you share how to unpivot columns? I ran out of memory using the column while for summry, while there's error in visual using measure. Filtering based on group of 60 columns
@theo Sure, let's say you do have an Index column or just add one. Right-click the header of the Index column in Power Query and then choose Unpivot other columns.
thanks for providing options. So far, only the measure works for me since I ran into memory issue when using columns since I am doing countif on 60col with 14mil rows
When using measure, although it works, should I be able to filter it? Since when I tried, it provides empty results.
@theo Well, I would think you would want to use or's || but not sure what you are doing in that measure will work, let me test as a measure. I thought you wanted a column.
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |