How to improve a slow DAX query using FILTER(ALLSELECTED()) for running total

[Anonymous]

Hi,

I'm new in DAX and I‘ve got a performance issue with this formula:

Value running total = 
CALCULATE(
SUM(Facts[Value]);
FILTER(ALLSELECTED(Facts);
Facts[Hour]<=MAX(Facts[Hour])
))

The response time is around 45 seconds. I want to have a running total by hours (over the current day) with a dependency of 3 slicers.

My report is based on only one table containing 9 columns and around 300'000 records. I tried to use one fact table and multiple dimension tables, but the performance didn't increase.

Is there another / better way to solve this?

Thanks!

[MFelix · Accepted solution]

Hi @Anonymous,

Try to change your measure to only get data from the Hour column.

Value running total = 
CALCULATE(
SUM(Facts[Value]);
FILTER(ALLSELECTED(Facts[Hour]);
Facts[Hour]<=MAX(Facts[Hour])
))

Regards,

MFelix

[Anonymous]

Hi @MFelix 

I am developing a measure to calculate running total of a measure which is very slow.

Base Measue = Var Hours = Calculate(Sum(FactTable[Hours])
Var Cost = Calculate(Sum(FactTable[Cost]),DimA[Attribute1] = "Services")

Return

Divide(Cost,Hours)

I have to create a Running Total of Hours  when its sorted from Desc to Asc by BaseMeasure

MainMeasure = Var CurrentRatio = BaseMeasure

Return

Calculate(Sum(FactTable[Hours]),Filter(ALLSELECTED(FactTable),BaseMeasure >= CurrentRatio))

This measure is very slow.

Please suggest some improvement points. Using this MainMeasure I have to develop around 10-15 more calculations.

[Anonymous]

Hi @MFelix

Very nice, it works - thank you!

I thought I have to use the entire table in ALLSELECTED() cause of the slicers.

Regards

Swiss_BI