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.
Good Afternoon,
I have the following measure: Ratio = EquipIDCount / TotalVisits. This is represented by the blue line for each date.
My page filter contexts are Date and Facility. I need to calculate a Benchmark across all Facilities.
I have calculated the red line using the following measure which removes all filter contexts (
Facility and Date).
Benchmark = Calculate( 'Work Item Measures'[EquipmentIDCount]/[TotalVisits], ALL(GeneralStatistics))
However, I want the Benchmark to retain the Date filter context and calculate the Benchmark across the date range selected.
If I use the AllExcept function with the Date filter,
Benchmark = Calculate( 'Work Item Measures'[EquipmentIDCount]/[TotalVisits], ALLEXCEPT('Calendar','Calendar'[Date]))
I simply get a repeat of the original measure where it is doing the calculation on each date and superimposing both measures.
Hoping someone can recommend how I would modify my Benchmark Measure to obtain a straight line calculation across the entire time period selected and not for each specific date.
Thanks in advance and good weekend!
Solved! Go to Solution.
Hi @rsbin
Create a date table which has no relationship with fact table,
create a measure
Measure 2 = CALCULATE( [idcount]/[visit],FILTER(ALL('Table 3'),'Table 3'[date]>=MIN('date'[Date])&&'Table 3'[date]<=MAX('date'[Date])))
slicer "facility" won't change the value of [Measure 2], the date slicer would change the [Measure 2].
Hi @rsbin
Create a date table which has no relationship with fact table,
create a measure
Measure 2 = CALCULATE( [idcount]/[visit],FILTER(ALL('Table 3'),'Table 3'[date]>=MIN('date'[Date])&&'Table 3'[date]<=MAX('date'[Date])))
slicer "facility" won't change the value of [Measure 2], the date slicer would change the [Measure 2].
Hi Maggie,
I am trying better to understand your solution. My entire data model is premised on a Fact Table and a related Calendar Table. I currently use this Calendar Table as my Slicer throughout my multi-page report. I have dozens of Measures that are based on the date range selected by the User.
Introducing another unrelated Date Table as you suggested, and using that as a Slicer, would affect all the other visuals I have on my report page - would it not?
Kind Regards,
Hello @v-juanli-msft
Thank you for the response. Over the weekend I was able to come up with a very similar solution.
First, I realized I was going down the wrong path with my SUMX solution above.
By trial and error, I realized I can use the Analytics Pane, to insert the following measure:
Benchmark = CALCULATE([EquipmentIDCount]/[TotalVisits], ALL(GeneralStatistics),
DATESBETWEEN( 'Calendar'[Date],MIN('Calendar'[Date]), MAX('Calendar'[Date])))
It looks like this is acting the same as way as your independent Date Table is doing.
Thank you much for taking the time to reply.
Best Regards,
So far, I have found a partial solution. The following gives me one of the running totals that I need.
RunningTotal_1 = SUMX(FILTER(ALLSELECTED('Calendar'[Date]),'Calendar'[Date]<=MAX('Calendar'[Date])),[EquipmentIDCount])
However, I need to remove the Facility filter context from this formula. I have tried different combinations of "ALL" and "ALLEXCEPT", but unable to get it to work as it should. Any suggestions much appreciated!
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |