Reply
Frequent Visitor
Posts: 12
Registered: ‎01-30-2018
Accepted Solution

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!


Accepted Solutions
Super User
Posts: 2,150
Registered: ‎09-19-2016

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!




View solution in original post


All Replies
Super User
Posts: 2,150
Registered: ‎09-19-2016

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!




Highlighted
Frequent Visitor
Posts: 12
Registered: ‎01-30-2018

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