A more efficient way to calculate COUNTIFS

[Anonymous]

Hi,

I am working with a dataset that has trip details in individual rows. I am trying to calculate which trips to the same city happened on overlapping days.

The logic is -

Count if :

1. check in date (entire data) <= check out date (current row)

2. check out date (entire data) >= check in date (current row)

3. hotel city (entire data) = hotel city (current row)

The calculated column formula in DAX that I am using is:

Count-Group Travel = CALCULATE(COUNTA(Hotel[Hotel City]),FILTER(Hotel,Hotel[Hotel City]=EARLIER(Hotel[Hotel City])),FILTER(Hotel,Hotel[Check out Date]>=EARLIER(Hotel[Check in Date])),FILTER(Hotel,Hotel[Check in Date]<=EARLIER(Hotel[Check out Date])))

I need this to be calculated over ~15000 rows, however, my system (8GB RAM) runs out of memory. Is there a more efficient to do this analysis?

Appreciate your help & suggestions!!

[Anonymous]

By the way... Why don't you calculate this in Power Query? It will be faster and more efficient, you will also get a better compression rate.

It's a well-known truth that you should calculate your columns before you import them into the model. Calculated columns via DAX should only be used if you don't have any other choice. The reason for this is that if you calculate it via DAX the compression rate is not as good as in the other case. And since your RAM is small, I'd suggest you do this in PQ.

Best

Darek

[Anonymous · Accepted solution]

Mate, you should learn a bit about DAX and data modeling. Seriously. A good worker knows their tools. You cannot be a good worker if you have no idea what your tools can do and how to operate them. RIGHT?

Having said that, your problem is that you use CALCULATE and thus create a context transition for each and every row in your table. CONTEXT TRANSITION is an expensive operation and this is why you run into problems. Another thing that slows down the code is filtering a full table instead of columns. Mate, NEVER FILTER A TABLE IF YOU CAN FILTER A COLUMN. That's the golden rule you should always have in mind when working with tabular models.

Here's something that does not use context transition:

 [Count-Group Travel] = -- calculated column
var __city = Hotel[Hotel City]
var __checkin = Hotel[Check in Date]
var __checkout = Hotel[Check out Date]
var __count =
    COUNTROWS(
        FILTER(
            Hotel,
            Hotel[Check out Date] >= __checkin
            && Hotel[Check in Date] <= __checkout
            && Hotel[Hotel City] = __city
        )
    )
return
    __count

Best

Darek

[Anonymous]

Thank you so much Darek - for the solution as well as the explanation. I am still a beginner at this, but I get your message - I definitely need to get some kind of foundational training on Power Query as well as DAX asap. Do let me know if you have some recommendations.

[Anonymous]

There's a free and quite good course from Microsoft on Power BI on edx.org. If you want to know exactly what DAX does behind the scenes and thoroughly understand tough (yet indispensable-to-know) concepts like context transition, then visit www.sqlbi.com (and get yourself The Ultimate Guide to DAX by The Italians). As much as Power Query goes, you'll have to do some digging yourself. I've not taken any PQ course but have studied the online documentation and experimented with M (even though I don't think you'll ever need this level of understanding).

Also, on GitHub you can find a lot materials on Power BI and related concepts, ranging from courses (free and paid-for), YT videos through to white papers and practical guides. Everything you could ever want to read to become a master.

Best
Darek

[Anonymous]

Thank you!