Forum Discussion

joerykeizer's avatar
joerykeizer
Icon for Helper II rankHelper II
9 years ago

Countifs in DAX

Hello,   I'm trying to convert a formula from excel to Power BI. In Excel: =COUNTIFS(A:A;A2;B:B;">"&B2)   The second range and ">B2" part are giving me some trouble. Your help would be very muc...
Need Help
joerykeizer's avatar
joerykeizer
Icon for Helper II rankHelper II
9 years ago

v-yulgu-msft 

 

I know the RankX works but I have a table with ~30K records and using it I keep getting memory errors, even with 32GB of RAM. It's just too heavy to run on large datasets. That's why I am still looking for a solution, RankX is not really an option for this.

 

v-haibl-msft sorry to bring you into this but you help me greatly before. Do you know if the "=Countifs()" from Excel can be replicated in DAX without using RankX? I've uploaded sample data in an earlier post.

richbenmintz's avatar
richbenmintz
Icon for Resident Rockstar rankResident Rockstar
9 years ago

Given that you do not want to use the Rankx function, another option is to rank your data either at source or thrrough a power query transformation. the following is a link to a pbix file with a grouped ranking function added to the data transformation,

https://1drv.ms/u/s!AhCeuF2piSWMgb9y1XvvuD9LwozRKA

Special thanks go to Chris Webb for his post on Nested Calcs in Power Query, https://blog.crossjoin.co.uk/2015/05/11/nested-calculations-in-power-query, the basis of this approach.

 

I hope this helps.