Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
theo
Helper III
Helper III

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)

col1col2col3col4col5col6col7col8col9col10range10range20range30
3591125790607102927COUNTIF(A2:J2,"<"&11)COUNTIFS(A2:J2,">"&10,A2:J2,"<"&21)COUNTIFS(A2:J2,">"&20,A2:J2,"<"&31)
1001778123795611658COUNTIF(A3:J3,"<"&11)COUNTIFS(A3:J3,">"&10,A3:J3,"<"&21)COUNTIFS(A3:J3,">"&20,A3:J3,"<"&31)
50585020713495256894COUNTIF(A4:J4,"<"&11)COUNTIFS(A4:J4,">"&10,A4:J4,"<"&21)COUNTIFS(A4:J4,">"&20,A4:J4,"<"&31)
9832671539672121841COUNTIF(A5:J5,"<"&11)COUNTIFS(A5:J5,">"&10,A5:J5,"<"&21)COUNTIFS(A5:J5,">"&20,A5:J5,"<"&31)
76331549806238442294COUNTIF(A6:J6,"<"&11)COUNTIFS(A6:J6,">"&10,A6:J6,"<"&21)COUNTIFS(A6:J6,">"&20,A6:J6,"<"&31)
1333482891147841629COUNTIF(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:

 

range10range20range30
212
230
011
121
011
132

 

can this be done?

 

1 ACCEPTED SOLUTION
v-yiruan-msft
Community Support
Community Support

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"

yingyinr_0-1629964467141.png

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"

yingyinr_2-1629964827060.png

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))

yingyinr_1-1629964802836.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

8 REPLIES 8
v-yiruan-msft
Community Support
Community Support

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"

yingyinr_0-1629964467141.png

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"

yingyinr_2-1629964827060.png

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))

yingyinr_1-1629964802836.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Greg_Deckler
Super User
Super User

@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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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])

@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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.