page filters based on calculated measures that is based on a slicer other than my date dim

[peterbrayton]

Hi,

   Is it possible to filter a page based on a calculated measure that is based on a slicer?  I have a requirement where the user wants months 1,2 4,5 7,8 10,11 in the time slicer greyed out if they select "AOP" from a different slicer as AOP is only valid for months 3,6,9,12.  I'm not sure if possible or if a different method could achieve the result.  The end user does not widh to spread aop equally over the months, they just want to prevent user from selecting specific months because aop vs CY actuals would not be a good comparison.  Thoughts? 

[Anonymous · Accepted solution]

One approach you can take is to embed the filtering into the measures themselves.  A method i've used it to set up a selection table that is put into a slicer.  I then have my measures check the selection table and depending on what values are still visible will depend what my measure does.

A simple example is to have a table 2 rows and 2 columns:  "Odd Months" & 1, "Even Months" & 2. 

Then you have a single selection slicer that lets you pick either "Odd Months" or "Even Months".  With the ID field, we can use a "MIN" or "MAX" function to get which ID is visible, and if nothing is selected what our default value is.

Then you could set up an Odd Months measure and an Even Months measure.  Lastly you create a Display Measure that is simply "If MIN (Selector Table ID) is showing 1 use [Odd Measure] else use [Even Measure].

[peterbrayton]

Ross,

   Thank you very much for your detailed response.  I did apply your solution and can get my AOP Vs Actuals and Delta to all show 0 when for Ex Period 1 is selected and AOP is also selected from the slicers.  That is better, but is there a way to filter the rows out, so instead of all 0's the rows disappear?  Page filters dont seem to take Calculated measures.  I thought of possibly doing a calculated table with a dax calc to somehow provide the conditional filtering on the fly, but pretty sure those tables dont change based on a front end slicer selection...  The answer you provided may be the best we can do with power bi for this particular requirement..Again thank you for your time, I do appreciate it!

Regards,

Peter.