Dynamically select column from different Dimension tables based on Slicer selection

[Anonymous]

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

[amitchandak · Accepted 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/

[v-shex-msft · Accepted solution]

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

[amitchandak · Accepted 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/

[Anonymous]

@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/