Limit the slicer to show values present in a fact table

[danisharoon]

Hi

I have a dimension shared by two facts. That dimension has 3 distinct values. Fact table A, has all 3 distinct values, and table B has 2 out of 3 distinct dimension values.

I am displaying the insights in two pages, one for each fact. When I put the dimension as a slicer on the canvas, it is displaying 3 distinct values on both pages, whereas it should display 3 on the first, and 2 on the second page.

What's the workaround to solve it?

Link to the .pbix file is as follows:

https://nflpk-my.sharepoint.com/:u:/g/personal/danish_haroon_nfoods_com/EVaReFdgHPJLnqf-4g7WcW0BPbJ8...

Danish

[danisharoon]

This ain't working

[v-rzhou-msft · Accepted solution]

Hi @danisharoon ,

I suggest you to create a measure to filter the slicer in Page2.

My Sample:

dim_prod_category:

Fact Table1:

Fact Table2:

Measure:

Slicer Filter In Page 2 =
VAR _CATEGORY_IN_Fact2 =
    VALUES ( 'Fact Table2'[product category name] )
RETURN
    IF (
        MAX ( dim_prod_category[product category name] ) IN _CATEGORY_IN_Fact2,
        1,
        0
    )

Add this measure into visual level filter of the slicer visual and set it to show items when value =1.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@danisharoon , Use a measure from fact as visual level filter and check for non blank

Take an example from this video if needed

https://www.youtube.com/watch?v=cyOquvfhzNM

[Boucounta]

Yes in this case one can use a filter from the fact table. But in other cases this solution above is of huge value!