Forum Discussion
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 much appreciated!
10 Replies
I think Query Editor would be more easy option than exploring to write complex DAX.
Sample Data and expected output would be highly appreciated.
I've uploaded some sample data.
The result I want is to rank products within a store by their sales. I'm trying to find an alternative to the RankX functionality which gets really slow for large amounts of data. I don't think the query editor is an option since the sales come from another table through a measure.
Hi joerykeizer,
I cannot dowload the sample data from the above link, so I assumed some data by myself in Excel.
In PowerBI, if the data table still looks like this, fisrt add a calculated column, then create a measure to count that new column:
Column3 = IF(CountIfs[Column1]=10 && CountIfs[Column2]>6,1,0)
Measure = CALCULATE(SUM(CountIfs[Column3]),ALL(CountIfs))In my test, because I do not know what is the meaning of the value A2 and B2, maybe a specific value or a calculated value, I directly used 10 and 6 in formula. You should change it based on your source data.
If I have something misunderstood, please share some sample data and your expected result, you can post the screenshot like the image above.
Thanks,
Yuliana GuThank you for your reply v-yulgu-msft
The link still works for me but I've uploaded it somewhere else as well -> New sample data link
Anyway so my expected result is this:
In your example A2/B2 should have actually been A1/B1. A1 is a categorical value and B1 is a measure.
Hopefully this will help you understand my question better.
countif in dax would be foo=calculate(countrows('table'), 'table'[bar] > var)
