Forum Discussion

dkr's avatar
dkr
Frequent Visitor
8 years ago
Solved

Dynamic Table based on Slicers

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.

  • 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.

4 Replies

  • anandav's avatar
    anandav
    Skilled Sharer

    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's avatar
      dkr
      Frequent Visitor

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

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee

    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