Slicers depend on each other

[Anonymous]

Hello all

I have several filters in my report.

I would like to have a dependency on the filters. When i change the "year" filter, it should refresh filter "subcat" with only the options available that have data to show.

(I made a new projcet where it works as expected with 3 filters)

My current project without any selected filters:

When i select a date:

It refreshes the Year correctly

But in the subcat are all options still available even though there are options without data:

I would like to hide the options without any valid values, is this possible? Why does it work with the year but not with the subcat?

Thank you and kind regards 

[PattemManohar]

@Anonymous Please provide the snapshot of your data model. Is the SubCat field (Slicer) is available in the same table where you are pulling other two filters ? If not, if it is in separate table then those two tables have a valid relationship ? Sample data and snapshot of your data model will be really helpful

[Anonymous]

Hi @PattemManohar

I will try to generate some sample data..

I have pulled two tables from our db, mainTable and a code Table. mainTable contains a column with SubCat, but its decoded. I made a "join" with the Code Table to show the encoded version. So in the SubCat Slicer abover its actually using a column of a different table

I just added another slicer with the original Subcat entry(decoded) and tried again, when doing the same filtering it shows an "Empty" entry.

[PattemManohar]

@Anonymous Yes please, sample data will be helpful.

[Anonymous]

@PattemManohar

It will take some time but i will upload it asap.

Just in meanwhile, as we probably found the issue (Slicer values from another table), is there a way to include them anyways?

[PattemManohar]

@Anonymous Good, then you need to have a relationship between those tables to filter values as per slicer selection (As you expect).

[Anonymous]

I already had a relation, see below.

Do i need to specify someting?

the red column "Text" is the one i use in my "SubCat" slicer.

[Capstone]

Is this a star schema ? Then have you tried bi directional filtering on the subcat table with your fact table ?

[Anonymous]

Hello @Capstone

Thank you for the quick response.

Unfortunately i dont know neither what a star schema nor directional filtering is..

[Anonymous]

Just tried it again, with the original SubCat column slicer it wont show any blank/empty results. But how'd came they are shown as Blank/Empty and not just hidden?