powerbi slicer update adapt to another slicer

Question

I have a dimension table with column A, B, C, D, E. And I create the start model to link column A to Table A, column B to table B, column C to table C, column D to table D, column E to table E. The 5 columns doesn't follow the hierarchy structure. And I need to create the slicers for each column based on the dimension table.

The data example distribution is:

A1, B1, C1, D1, E1
A1, B2, C3, D3, E2
A2, B2, C2, D2, E3
A2, B3, C2, D3, E4

The slicer A may first have value A1, A2, and then if the user select A1, the slicer B may just provide choice B1 & B2 (B3 may be trimmed due to slicer A). How could I achieve this with each slicer change due to the up level slicers?

Actually I create a measure count for the table and add the the filter with condition > 0 for each slicer. This works at the beginning, but due to the single select setting for the slicer, after all the slicers have been selected, there is no choice to change for all the slicers.

What I want is that slicer A may contains choice A1, A2, and for slicer B if slicer select A1 then it will provide choice B1, B2. And if slicer B select B2 then slicer C have option C2, C3, etc. How could I achieve? Thanks.

Answer 1

Here I would like to thank Marcus and Ozan's answers, and they are helpful for PBI development. Here for this question, I did think more the day and had one solution.

I may need to created more derived dimension tables based on the full dimension table:

• Slicer A: a new dimension table DA with only distinct column A from the full dimension table, and set filter row count table DA > 0
• Slicer B: a new dimension table DB with only distinct columns A, B from the full dimension table, and set filter row count table DB > 0
• Slicer C: a new dimension table DC with only distinct columns A, B, C from the full dimension table, and set filter row count table DC > 0
• Slicer D: a new dimension table DD with only distinct columns A, B, C, D from the full dimension table, and set filter row count table DD > 0

In this way we could from slicers top down to select the value candidates from the data for each. Slicer A may have all the distinct column A values, and Slicer B may just have the column B values given column A value, and so on.

Answer 2

Here is my solution based on conventional single-direction relationships that make the data model easier to work with. Generally, avoiding bi-directional filters is a good idea, since these can introduce a lot of complexity to other aspects of model design.

We start off with a simplified model to demonstrate the technique, it is not as complex as your own, but has the necessary components:

Here Table A and Table B are DAX tables, but these should ideally be an output from Power Query instead, to avoid such components in the data model. However, the tables are calculated using DISTINCT:

Table A = DISTINCT ( 'Table'[A] )
Table B = DISTINCT ( 'Table'[B] )

In our canvas view, we establish our test visuals: a table visual based on Table and slicers based on Table A and Table B:

To determine which values in Table A and Table B that are shown, we will add a measure that is evaluated for each of the available slicer options that will determine whether the option represents available data in Table based on the current filtering context.

We do this by simply counting rows in Table for each option. We add the following measure that returns 1 if there are any rows visible for the current slicer option:

Slicer Filter = 
IF ( COUNTROWS ( 'Table' ) > 0 , 1 )

We apply this measure as a visual level filter on all the relevant slicers, shown here for the Table B slicer. This will force the slicer to only show values in Table B for which there are visible rows in Table, without having to use bi-directional filters:

Now if we select A1 or A2 in Table A slicer, the Table B slicer updates accordingly:

Apply the same visual level filter on all relevant slicers, and you have your solution without introducing complex changes to your data model relationships.

Answer 3

Let's say you have created your tables like this:

Main Table:

And Other tables created by calculated tables using DAX:

Table A = VALUES(MainTable[A])
Table B = VALUES(MainTable[B])
Table C = VALUES(MainTable[C])
Table D = VALUES(MainTable[D])
Table E = DISTINCT(MainTable[E])

Now Let's model the tables:

See picture

Now let's test it with slicers:

I hope This is what you want!