cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Swiss_BI Frequent Visitor
Frequent Visitor

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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

Hi @Swiss_BI,

 

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




2 REPLIES 2
Super User
Super User

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

Hi @Swiss_BI,

 

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Swiss_BI Frequent Visitor
Frequent Visitor

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

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