Countifs in DAX

[joerykeizer]

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!

[v-yulgu-msft]

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 Gu

[joerykeizer]

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

[v-yulgu-msft]

Hi @joerykeizer,

From the pbix file you uploaded, I found that the measure RankX has returned your expected result. So, do you have any other question?

RankX = rankx(ALLEXCEPT('Table','Table'[Store]),[SumSales])

Thanks,
Yuliana Gu

[joerykeizer]

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

[MattAllington]

@joerykeizer a few comments from me.

1.  RANKX is the correct formula for what you need.

2.  30k records is a tiny tiny data model (micro in fact).

If you have performance issues with this size data model, it is most likely because your table strucutre is not optimal.  How many columns do you have in your data table?  I am guessing it is quite a few.  Your aim should always be to only bring in columns you need, and also unpivot data when possible.  Generally speaking, a table with 5 columns and 300k rows will be much faster than a table with 15 columns and 30k rows.

Can you share your table structure?

[shedoken]

Hi Guys, 

I am very new to PowerBI and knows nothing about DAX. I have the excel formula in the image below. Basically i want to know how to write a DAX formula that counts how many times the SERIAL NUMBER occured in a specific month, for the example below i filtered the pivot only October 2016 but i will be displaying 13months as you can see in the table in the top.

[richbenmintz]

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.

[richbenmintz]

countif in dax would be foo=calculate(countrows('table'), 'table'[bar] > var)

[BhaveshPatel]

I think Query Editor would be more easy option than exploring to write complex DAX. 

Sample Data and expected output would be highly appreciated.

[joerykeizer]

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.