countifs based multiple columns criteria

[theo]

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?

[Anonymous · Accepted 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

[Greg_Deckler]

@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

[theo]

@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?

Measure1 = COUNTX(FILTER('7Jun_output',

'7Jun_output'[col1]<11 && '7Jun_output'[col2]<11),'7Jun_output'[col1])

[Greg_Deckler]

@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

[theo]

@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

[Greg_Deckler]

@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.

[theo]

@Greg_Deckler 

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.

[Greg_Deckler]

@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.