Slicer Filtering Another slicer

[GiTchitchinadze]

Hi, 

I did my research on forum but I couldnt find solution for my problem. I have a main Data table and 3 small tables with distinct values to filter the data table. 1. Region, 2. City, 3. District. In each of those tables I have single distinct value column of regions cities and districts. 
My goal is to make three slicers that will filter each other. So if I tick California for instance in Region, in city slicer it will only show Los Angeles and other cities of California. And if Tick Los Angeles it will only show Los Angeles's districts in district slicer if it makes sense. 
I wonder how should I create the filter tables. Should I create a fourth table that will connect these 3 tables with each other or should I add cities in region table and districts in city table.

And what kind of relationship do they need.

[jdbuchanan71]

If using just 1 of the checks works for you I would do that, it will be faster for the model to count the rows of 1 table instead of 4.

[jdbuchanan71]

The slicer filter formula is just counting the rows of the fact table.  Think of it like this, if you had a model that had 3 tables in it.

• Dates
• Customers
• Sales

  Dates and Customers are both linked to Sales.  You add a slicer to your report from the Dates table for Year but you only want to show years in the slicer where there are sales.

  You also add a slicer from the Customers table for Country.

  Your sales table has amounts for all years from 2010 - 2022 for customers in the UK but you only started selling in Germany in 2021. 
  If you pick Germany in a country slicer the Year slicer would only show 2021 and 2022 because the slicer filter measure would only return a count for 2021 and 2022 since you picked Germany.

[GiTchitchinadze]

Slicer filter formula did the job. Thank you so much!!!

[Ayappan]

@GiTchitchinadze 

What is that slicer filter formula -can you please explain 

i am in same situvation i need to filter slicer with another slicer - Is it posible to do it without merging all table inot 1 table ?

Thanks .

[GiTchitchinadze]

Hi,

I solved my problem without merging tables. I just created measure using countrows formula as you can see in accepted solution comment. I think what that formula does is that it makes bi understand that there are blanks in columns you are trying to filter. Just create a measure using countrows formula and drop it in every slicers filter pane and choose is not blank option as showed above and it will do the job I hope. 

[jdbuchanan71 · Accepted solution]

@GiTchitchinadze 

Combine them all into one table that has all 3 fields.  If you do that and have a slicer for each field, when you pick CA the city silcer will be filtered to only show cities in CA.

Another option would be to have a measure that counts the rows in your main data table then use that as filter on each of the slicer.  You would set that to 'is not blank' and when you picked CA in the region the cities would be filtered.  In my example below I have a measure that just counts the rows of the sales table.  When I apply that as a filter to the slicer you can see it takes out the dates that do not have any records.

Slicer Filter = COUNTROWS(Sales)

[bhargavanand]

Hi, if I have four main tables which have different slicer tables connected. Then do I need to add that Slicer Filter measure in all 4 main tables and then add them to Filter for all slicers?

Thanks in advance.

[jdbuchanan71]

You could make a single measure that checks all 4 detail tables at once and use that filter measure on all the slicers.

Filter Measure = 
    VAR _Claim = COUNTROWS ( vCLAIM )
    VAR _Invoice = COUNTROWS ( vINVOICEDETAIL )
    VAR _Dist = COUNTROWS ( vINVOICEDISTRIBUTION )
    VAR _Agg = COUNTROWS ( vREINSAGGDETAIL )
    VAR _Check = _Claim * _Invoice * _Dist * _Agg
RETURN 
    IF ( _Check = 0, BLANK(), 1 )

If any of the counts return 0 the _Check will be 0 and give the blank and you can set the filter to 'Is Not Blank'

[bhargavanand]

Even adding one of those variables to the slicer filter works for me. Is it okay, or is it better to make product of those 4 variables and then add it to the slicer filter?