Why is my Measure so Slow?

[Anonymous]

Hi all,

I'm working on a formula to calculate the lifetime running total of accrual earnings. I have a formula that works as intended but is way too slow to actually implement. Can someone teach me a more efficient way of accomplishing the same thing?

Sick Earned Running = 
VAR maxdate = MAX('Calendar'[Date])
VAR eeid = SELECTEDVALUE('Accrual Details (2)'[Employee ID])
VAR earned = 
SUMX(
                FILTER( 
                    ALL('Accrual Details (2)'), 
                    'Accrual Details (2)'[To Date] <= maxdate && 
                    'Accrual Details (2)'[Employee ID] = eeid && 
                    'Accrual Details (2)'[Accrual ID] = "2" && 
                    'Accrual Details (2)'[Projection ID] = 0 && 
                    'Accrual Details (2)'[Action] = "Earned"), 
                    [Amount]) 
RETURN
earned

[Anonymous]

DAX will be fast in case the model is correct (star schema) and it's correctly written. Here's what it should look like, more or less:

// Dimensions connected to your fact table 'Accrual Details':
// Employee
// Accrual
// Projection
// Action
// Calendar should probably be connected to 'Accrual Details'[To Date].

[Total Amount] = SUM( 'Accrual Details'[Amount] )

[Sick Earned Running] =
VAR __maxDate = MAX ( 'Calendar'[Date] )
VAR __oneEmpVisible = HASONEVALUE ( Employees[Employee ID] )
VAR __result =
	CALCULATE(
		[Total Amount],
		Calendar[To Date] <= __maxDate,
		KEEPFILTERS( Accrual[Accrual ID] = "2" ),
		KEEPFILTERS( Projection[Projecion ID] = 0,
		KEEPFILTERS( Action[Action] = "Earned" )
	)
RETURN
	IF( __oneEmpVisible,  __result )

Whether you should use KEEPFILTERS or not depends on your requirements.

Best

D

[Anonymous]

Hi there. You want fast DAX? Well, then you have to have a correct model built according to Best Practices. There is no other way.

Some golden rules of dimensional data modelling:
1. Slicing only by dimensions, never directly on fact tables.
2. All fact tables' columns hidden, only measures visible if put in the fact.
3. Using CALCULATE with simple filters. No explicit iterations.

If I get a chance, I'll show you the DAX as it should look on a correct model.

Best
D

[Anonymous]

If you don't need to caclulate [Amount] on a row level It's usually better to use

CALCULATE(SUM([Amount]), FILTER(...

Or if [Amount] is a measure

CALCUALTE([Amount], FILTER(...

If you need to do it on row level, try 

CALCULATE(SUMX('Accrual Details (2)', [Amount]), FILTER(), FILTER(), FILTER())

The filters can be applied to the dimension tables instead of fact table if possible.

[Greg_Deckler]

Try breaking you FILTER statement into multiple steps with VAR. First filter out the things that will generally eliminate the most rows. For example, if there are lots of rows that are generally less than or equal to the date selected versus there will be less rows if you filter by Employee ID, then create a table variable that filters by Employee ID, then filter by Date.

[Anonymous]

Ok that all makes sense, but what I'm having trouble with is the section of the code that defines the cumulative calc (the date <= Max( date) portion)

How do I accomplish this while using variables as tables?