Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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.
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