Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
@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
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
@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/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |