Forum Discussion

rsbin's avatar
rsbin
Icon for Community Champion rankCommunity Champion
6 years ago
Solved

Calculating Benchmark - ALLEXCEPT not quite working

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...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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].

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
6 years ago

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].

 

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
  • rsbin's avatar
    rsbin
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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,

  • rsbin's avatar
    rsbin
    Icon for Community Champion rankCommunity Champion
    6 years ago

    v-juanli-msft 

    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,