How to Make FY and LocationCode Slicers Dynamic Based on Selected Country in Power BI

[GanesaMoorthyGM]

Hi Community,

I have a fact_sales table along with dimension tables for Country, Location Code, Month, and FY in my data model.

And this is my data modelling

Here’s what I want to achieve:

• When I select a Country (for example, "US") in a slicer, I want the FY slicer to dynamically show only the fiscal years relevant to that country.
  For instance, my overall data has FY from FY21 onward, but the US started only from FY23. So when I select US, the FY slicer should show only FY23 and beyond.

• Similarly, when I select a Country and an FY (say FY24), I want the Location Code slicer to dynamically display only location codes that are relevant to the selected country and fiscal year.

Is this possible to achieve with Power BI slicers? If yes, could someone please guide me on how to implement this dynamic filtering behavior?

Thanks in advance!

[v-mdharahman]

Hi @GanesaMoorthyGM,

Thanks for reaching out to the Microsoft fabric community forum.

It looks like the slicers are not automatically cascading based on actual data availability in fact_sales. As @Ashish_Mathur, @FBergamaschi, @Shahid12523 and @MasonMA have rresponded to your query, kindly go through their responses and check if your issue can be resolved.

I would also take a moment to thank @Ashish_Mathur, @FBergamaschi, @Shahid12523 and @MasonMA, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.

[Ashish_Mathur · Accepted solution]

Hi,

Write this measure

Total = sum(Data[sales])

Drag this measure in the filter pane of the FY slicer and set the criteria to Not blank.

Hope this helps.

[FBergamaschi]

Hi @GanesaMoorthyGM 

I would not use bidirectional relationships as the risk of bad perfomance and ambiguity are always there. On the contrary, I would follow @MasonMA  suggestion and create a measure to use as a filter.

On that, as this is a complex DAX topic, I just want to make it clear to you that, following suggestion from @MasonMA, you will define the filters applied to the model before evaluationg the measure and check its value to be equal to 1. You will deinfe this filters by grouping columns in the visual.

If you group Country, Fiscal Year and Location, the visual will show only rows for which Country, Fiscal Year, Location provide a non empty Sales table.

So my post here is just a way to help you understading what a measure filter is, hope I helped clarifying what @MasonMA is suggesting. I agree with his suggestion

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[Shahid12523]

Use cascading slicers:

1.Set relationships (Country → Location → fact_sales, FiscalYear → fact_sales) to Both cross filter direction.

2.Add slicers: Country, Fiscal Year, Location.

3.Enable Edit Interactions so slicers filter each other.

Result: Selecting a Country filters Fiscal Years, and selecting Country + FY filters Location codes.

If relationships get messy, use calculated slicer tables with CALCULATETABLE + ALLSELECTED.

[MasonMA]

Hello @GanesaMoorthyGM 

If not enabling Bi-directional crossing filtering (which i wouldn't unless i'm sure there's no impact on performance), one of the ways to achieve this would be creating one Measure like below and apply the Measure as a visual-level filter (set it to 'is 1') for your 'FY Slicer'. 

INT(
    NOT(
    ISEMPTY('fact_sales'))
)

 This is an example i have on my report. 

thanks