Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |