Forum Discussion
Dynamically select column from different Dimension tables based on Slicer selection
Hello Experts,
How to dynamically select column from different Dimension tables?
I want the Bar chart Y-axis to dynamically pick up between Month, Week and Category columns based on Slicer selection. Slicer should contain 3 respective texts in dropdown: Month, Week, Category
Month and Week values come from dimension table Dim_Date - this is created in DAX
Category values comes from dimension table Dim_Product - imported table
Sales amount values comes from fact table Fact_Sales - imported table
Data model
Expected results:
When "Month" is selected in Slicer:
| Month | Sales amount |
| JAN 2021 | 100 |
| FEB 2021 | 0 |
| MAR 2021 | 300 |
| and so on... |
When "Week" is selected in Slicer:
| Week | Sales amount |
| Week 1 | 10 |
| Week 2 | 20 |
| Week 3 | 30 |
| Week 4 | 40 |
| Week 5 | 0 |
| and so on... |
When "Category" is selected in Slicer:
| Category | Sales amount |
| A | 60 |
| B | 40 |
| C | 50 |
| and so on... |
Thanks
Nirmit
Anonymous , Dynamic axis need bookmarks , in direct query you have some different solution
Dynamically change chart axis in Power BI
bookmark -https://blog.crossjoin.co.uk/2018/04/20/dynamically-changing-a-chart-axis-in-power-bi-using-bookmarks-and-buttons/https://datamonkeysite.com/2020/10/22/change-dimension-dynamically-using-parameter-in-powerbi/
Hi Anonymous,
Perhaps you can try to create a dynamic attribute report based on unpivot column, then you can use these unpivot fields with custom measure expression to your requirement:
Dynamic Attributes In A Power BI Report
Regards,Xiaoxin Sheng
3 Replies
Anonymous , Dynamic axis need bookmarks , in direct query you have some different solution
Dynamically change chart axis in Power BI
bookmark -https://blog.crossjoin.co.uk/2018/04/20/dynamically-changing-a-chart-axis-in-power-bi-using-bookmarks-and-buttons/https://datamonkeysite.com/2020/10/22/change-dimension-dynamically-using-parameter-in-powerbi/
amitchandak Thanks for the response.
Bookmarks are indeed an option. I however wanted to do it at Model level. I got another article link from inside the link you shared - which actually does it via DAX Model approach using TREATAS function. Using this in combination with CROSSJOIN, I could achieve what I was looking for. Thanks again.
https://www.kasperonbi.com/dynamically-switching-axis-on-visuals-with-power-bi/
Hi Anonymous,
Perhaps you can try to create a dynamic attribute report based on unpivot column, then you can use these unpivot fields with custom measure expression to your requirement:
Dynamic Attributes In A Power BI Report
Regards,Xiaoxin Sheng