Forum Discussion
Solved
countifs based multiple columns criteria
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?
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
8 Replies
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])+0Greg_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?
Measure1 = COUNTX(FILTER('7Jun_output','7Jun_output'[col1]<11 && '7Jun_output'[col2]<11),'7Jun_output'[col1])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.
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
