One slicer to filter two unrelated columns

[lemaribdb]

Hi!

I have 2 tables, they are not related to each other.
I want to have a slicer that will filter Table1[Region] for "EMEA" or Table2[Unit] for "GER". So the whole page has either one of the two perspectives. It seems simple, like a 2 in 1 slicer, either filter one column or the other but I have tried many approaches and none of them work.

Approach 1. Bookmarks. Have bookmakrs that when selected, affect 2 slicers, one with Region, other with Unit. When one bookmark seletcted, slicer with Region select EMEA, Slicer with Unit is blank, and the other way around for second slicer. And controls licers with a button -> bookmark navigator. It's a nice approach but when used on multiplate pages, the button state won't sync, meaning even if on Page 1 user selectes EMEA (when it was GER), the slicers will slice on all the pages, but the button itself will stay on GER on other pages.

Approach 2. Field Parameters. Have both Region and UNIT as field parameter. Sadly it's not possible so click "show values" for slicer when you have a field paramtered that has 2 unrelated columns.

Approach 3. Calculated tables that have relationship to the two tables. This pretty hard, if there columns were from the same table, it's a peace of cake. But creating such table that will somehow operate on good cardinality that is connected to two tables, I find that too difficult. I was thinking about some cross joins, maybe that is the only solution? And then connecting the two tables, so it can be used in field parameter, or creating a bridge?

I want to avoid solutions where I need to calculate a measure and use this measure as visual filter on all the visuals, it should work as slicer so filtering the whole page context.

[Anonymous]

Hi @lemaribdb ,

Thanks @FreemanZ  for the quick reply. I have some other thoughts to add:

(1) We can create a table.

Slicer = 
var _1=DISTINCT(SELECTCOLUMNS('Table 1',"slicer",[Region]))
var _2=DISTINCT(SELECTCOLUMNS('Table 2',"slicer",[Unit]))
RETURN UNION(_1,_2)

(2) Create model relationships.

(3) Then the result is as follows.

If I misunderstand what you mean, please clarify in a follow-up reply.

Best Regards,

Neeko Tang

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

[FreemanZ]

hi @lemaribdb ,

can you elaborate this: have a slicer that will filter Table1[Region] for "EMEA" or Table2[Unit] for "GER" ?

[lemaribdb]

A slicer that would have VALUES() of both Region and Unit, with single selection, so only one of them can be selected. As one column if one legal entity perspective, second column is other perspective. And we only care about 1 specific entity from Region table, and 1 specific entity from Unit table. And allowing user to simply select one or the other perspective would be awesome.