Dynamic measure filtering

[powerlight1]

Hi Community,

I am trying to create a report with multiple slicers that filter the underlying table dynamically.  Let me explain.

I have an underlying sales by date table:

Firstly, the user would select from the State and Date slicers.  For example 

Then, I will have a third filter that have predefined Average Sales Band, e.g. 100-150, 150-200, 200-250, so on ...

Lastly, I will then need to create a third slicer to filter the Average Sales by State and Product.

I can not create a summarized/calculated table because the date/period will need to be dynamic based on the the Date slicer.

What I have done is created a measured called Average Sales, which is just AVERAGE(SALES) and based on Slicer1 & Slicer2 (above) I am able to produce this results:

What I next want to do is that in Slicer3 the predefined Average Sales Band and if the user select "100-150" then only return Banana and Oranges.

How do I complete the last step?

[v-tejrama · Accepted solution]

Hi @powerlight1,

Thank you for explaining your requirement so clearly and also for providing the sample data. I tried the same scenario in Power BI and your logic is absolutely right. The only final step you were missing was after creating your IsInSelectedBand measure, you need to select the table visual (which shows product and average sales), and then drag IsInSelectedBand into the "Filters on this visual" pane, and set it to 1.

 Once this is done, the table will only show those products whose average sales fall into the selected band, based on the state and date filters you applied in slicers. I tested with your values and it’s working exactly as expected.

Please find the attached pbix file for your reference.

Thanks again for the nice scenario!

[v-tejrama]

Hi @powerlight1 ,

Just checking in have you been able to resolve this issue? If so, it would be greatly appreciated if you could mark the most helpful reply accordingly. This helps other community members quickly find relevant solutions.

Thank you.

[powerlight1]

Perfect! Thanks again.