Performance (slowness) based on a DAX Measure

[analyst44]

Greetings!

I've been working on a model that has been discussed on this board here. This is not to do with the model itself, but rather something I've come across that is a more general DAX question. I am posting it separately given there is a lot of detail in the other thread that I don't want people to think they need in order to assist with this.

Everything goes amazingly well in my model until I add the following DAX Measure:

=CALCULATE(
    DISTINCTCOUNT(fCradle[Date]), 
      FILTER (
        fCradle,
        fCradle[Total hours] <> 0 && fCradle[Date] >= RELATED ( dStaffList[Hire Date] ) && fCradle[Date] <= RELATED ( dStaffList[Term Date] )
         )
)

fCradle is about 50,000 rows.

Every other table referenced here is not very big at all:

• dStaffList is 122 records
• dDate (official Calendar Table) is 368 records
• dTimeType is 186 records
• dWeekBreakdown (related to dDate) is 54 records

I recently rebuilt the model from scratch and noted when it started to take a much longer time than I'd expect when adding a measure. The above is the first time it happens (downstream measures depend on it) and I am curious as to why. It doesn't seem particularly complex, the tables are not huge in terms of records. The model would seem to be very efficient given the sizes and relationships. It's basically a Star Schema with only dWeekBreakdown being related to dDate before fCradle.

I did research on DISTINCTCOUNT and it seems like a perfectly fine measure so I am guessing it has something to do with the RELATED function and trying to discern if a value is between two dates? If so, is there any other way to do this? It takes the model like 20-40 seconds when I add it to a pivot table and it's driving me crazy. Had I not used PowerPivot, it'd be much faster using lookups, even in the fact table.

Any ideas?

Thanks in advance for viewing!

 

[akice]

If you are interested, here is a few posts from the masters that may shed some light on what is going on.
Filter Arguments in CALCULATE - SQLBI

From SQL to DAX: Filtering Data - SQLBI

FILTER vs CALCULATETABLE: optimization using cardinality estimation - SQLBI

The last article i list may be the most relevant to what you are asking. Hope this helps.

 

[analyst44]

If you are interested, here is a few posts from the masters that may shed some light on what is going on.
Filter Arguments in CALCULATE - SQLBI

From SQL to DAX: Filtering Data - SQLBI

FILTER vs CALCULATETABLE: optimization using cardinality estimation - SQLBI

The last article i list may be the most relevant to what you are asking. Hope this helps.

Thanks so much. I've been hearing about these guys for awhile and have read a bunch of their posts. I don't think I've seen these, so I will absolutely check them out. There's so much about optimization out there but I didn't find anything specific to this need.

Appreciate the really fast response!

 

[akice]

SQLBI is a great resource. Whenever I start to feel like I know a lot about DAX I go there and read some posts and come back down to earth a lot more humble.

 

[analyst44]

SQLBI is a great resource. Whenever I start to feel like I know a lot about DAX I go there and read some posts and come back down to earth a lot more humble.

Cannot wait to get one of their books. They seem awesome. I am still going through a second foundational one (both have not been by them).

Humility on DAX/Powerpivot won't be a problem for me for a very long time! I feel like I'll be a noob for months and months!