Slicer filters aren't work with dynamic chart

[Alyona_BI]

Hi, Power BI guru!

I have the task to create dynamic chart, where user could select any dimension and see the result with help of bar chart. 

This is my simple data model: 

I did this task doing the following: 

1. I created new table for dimensions which could be used in selection: 

SelectedDimensions =

UNION(

DISTINCT(SELECTCOLUMNS(Products; "Value"; Products[Category]; "Dimension"; "Category"));

DISTINCT(SELECTCOLUMNS(Products; "Value"; Products[Brand]; "Dimension"; "Brand"));

DISTINCT(SELECTCOLUMNS(Products; "Value"; Products[Subbrand]; "Dimension"; "Subbrand"))

)

 The result: 

2. I created new measure (where for which dimension in selection i created virtual relation between values in [Selected dimesions] table and [Products] table): 

Avg discount dynamic = SWITCH(

TRUE();

SELECTEDVALUE(SelectedDimensions[Dimension]) = "Category";

CALCULATE([Avg basic discount in money];TREATAS(VALUES(SelectedDimensions[Value]);Products[Category]));

SELECTEDVALUE(SelectedDimensions[Dimension]) = "Brand";

CALCULATE([Avg basic discount in money];TREATAS(VALUES(SelectedDimensions[Value]);Products[Brand]));

SELECTEDVALUE(SelectedDimensions[Dimension]) = "Subbrand";

CALCULATE([Avg basic discount in money];TREATAS(VALUES(SelectedDimensions[Value]);Products[Subbrand]))

)

The result is ok: 

But if I select the dimension "Category" for chart and want to use slicer "Category" to see only several categories, it doesn't filter the chart and show me all categories: 

If i still have "Category" in dimesions and select something in "Brand" or "Subbrand" slicers then it filters the chart in correct way. For example: 

What I need to change in measure calculation to use slicer "Category" with selection "Category" as dynamic dimension in chart?

[mahoneypat]

You could have a single slicer (instead of 3), using the Value column of your DAX table.  When you choose a Dimension, that should filter to just those values.

Regards,

Pat

[Alyona_BI]

Hi @mahoneypat! Thanks for your idea. But in my description is a simple data model. In reality i have a lot of slicers and not related only to Products table, but also to several other different tables. And I need to give users the possiilty to filter with help of them in parallel. So i couldn't change all of them with help of Value column in slicer.  

Do you have any other ideas? 

Thanks in advance! 

[amitchandak]

@Alyona_BI , A table can not take slicer values.

The best you can do is to create this table as var table in measure.

[Alyona_BI]

Hi @amitchandak!

Could you help me with example for this var table in measure (related to my task)? 

Thanks in advance!

[amitchandak]

@Alyona_BI ,

In case you are looking for dynamic axis

You can explore these solution

1. You can use bookmark . https://radacad.com/bookmarks-and-buttons-making-power-bi-charts-even-more-interactive

2. You can unpivot the data to move columns to rows. - https://www.youtube.com/watch?v=6jeSIRpjv0M

3. There is new solution, but that work for direct query( May add one table as direct query for that - I have not tested)

https://datamonkeysite.com/2020/10/22/change-dimension-dynamically-using-parameter-in-powerbi/

[Winniethewinner]

Hi @amitchandak, I'm having exactly the same issue and came across this post. After the research and I figured out with this approach: View solution in original post. 

However because of the size of my dataset (4M rows) and the dynamic Axis I need, the dimension table would size 32M, this takes up a lot resource and slow down the performance. 

I'm wondering do you have any suggestions to make it work better? Thanks in advance.