Dynamic Table based on Slicers

[dkr]

Please excuse the newbie question...

I have created my first report, with a slicer which work as expected.  I am now trying to take it one step further by adding another slicer, but have hit a stumbling block and after a lot of googling I haven't found the answer I need.

Slicer Tables

Table A

Column 1

Value 1

Value 2

Value 3

Table B

Column 1      Column 2

Value 1          A

Value 1          B

Value 2          A

Value 2          C

Datatables

Table C

Column 1        Column 2    Column 3

Value 1          A                   Z

Value 1          B                   X

Value 2          A                   Z

Value 2          C                   Y

Table D

Column 1        Column 2    Column 3

Value 1          A                   ZZ

Value 1          B                   XX

Value 2          A                   ZZ

Value 2          C                   YY

All tables can have a relationhip with with Table A using Column 1

My slicers are derived from table A & Table B

When I select something in Slicer 1 (Table A), it changes the values in Slicer 2 (Table B) which is great. 

But because Table B has multiple values in column 2 I cannot create a relationship with tables C & D.  I think I need to create an intermediary dynamic table which just holds the unique values from Table B after a selection has been made on Table A and use these results in my 2nd slicer instead of Table B.  However, I cannot workout how to get the values from Table B based on the selection on Table A. 

Am I going about this the wrong way?

Many thanks for any help/pointers you can provide.

[v-huizhn-msft]

Hi @dkr,

>> I think I need to create an intermediary dynamic table which just holds the unique values from Table B after a selection has been made on Table A and use these results in my 2nd slicer instead of Table B. 

 It's possible to create a dynamical calculated column/table based on slicer, please review this knowledage base: Calculated Column/Table Change Dynamically According to Slicer Selection in the Report.

You can create a measure to get the selected value of a slicer by using SELECTEDVALUE.

Best Regards,
Angelia

[Anonymous]

Calculated Column/Table Change Dynamically According to Slicer Selection in the Report. This link is not accessible. Do you have any other reference for this.

Thanks,

Joe

[anandav · Accepted solution]

Hi @dkr,

As you have mentioned amy be you can create a new table with unique values of table B and use that to create relationship with tables C and D. You will then use the new table valuces in Slicer B.

 You can create a new table by selecting the New Table buttion in Modelling tab.

Table = Values('TableB'[Column1])

If you can upload a pbix  file with sample data to a cloud drive and share the link, we could try to help.

[dkr]

Many thanks for your help.  It was the Bi-Directional setting on the relationship that I had overlooked.