cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nirmit27
Helper II
Helper II

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

nirmit27_0-1626874530834.png

Expected results:

 

When "Month" is selected in Slicer:

MonthSales amount
JAN 2021100
FEB 20210
MAR 2021300
and so on... 

 

When "Week" is selected in Slicer:

WeekSales amount
Week 110
Week 220
Week 330
Week 440
Week 50
and so on... 

 

When "Category" is selected in Slicer:

CategorySales amount
A60
B40
C50
and so on... 

 

 

Thanks

Nirmit

2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

View solution in original post

v-shex-msft
Community Support
Community Support

Hi @nirmit27,

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
Community Support

Hi @nirmit27,

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
amitchandak
Super User
Super User

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

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

 

 

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!