cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
satishkoneri
Frequent Visitor

Switch Dimension from multiple dimension tables

Hi,

I am new to Power BI and I have a situation where I need to develop a very flexible dashboard as below:

1. Switch between various metrics type (eg: Amount, Quantity)

2. Switch between various metrics (eg: curryear, lastyear, yearminus2)

3. Switch between various dimensions (eg: custID, custName, custType, prodID, prodName, prodType, etc)

 

Data Model is:

FactTable - custID, prodID, currYearAmt, currYearQty, lastYearAmt, lastYearQty, yearMinus2Amt, yearMinus2Qty

custDimTable - custID, custName, custType 

prodDimTable - prodID, prodName, prodType 

Above is just as an example, there are about 20 different dimension attributes required to switch, which are connected using various dimention tables 

 

I was able to solve 1 & 2 using disconnected table trick and calculated measures. For 3, looking around in this community forum I could see an option to create duplicate table and unpivot. However this option works if all my dimensions are in the same table unlike in mycase. 

 

Please can someone advise if there is a solution to create a slicer and switch dimensions, which are connected to multiple dimension tables.

 

Any help will be greatly appreciated.

 

4 REPLIES 4
amitchandak
Super User
Super User

@satishkoneri ,

For switching Column/Measure: https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...

For Switching viewby/groupby/Axis, I think you have to use bookmarks

https://radacad.com/bookmarks-and-buttons-making-power-bi-charts-even-more-interactive

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 !!
Greg_Deckler
Super User
Super User

That's a fairly sophisticated issue, can you provide sample data to experiment with? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

@Greg_Deckler 

Sorry few other things missed in my original post:

There are multiple fact tables as below:

- FactActuals: stores actual qty and amount for current and last year

- FactPredM<x>: stores predicted qty and amt generated every month as a new table (sample included for 3 months)

Data is too big to combine, so I plan to keep them seperate but link using custID and  prodID.

 

Not sure how to attach file here, see if you can download an excel file from this link (manuall created sample data):  http://sendanywhe.re/C5NKLHTI   

@amitchandak  - Thanks for your prompt reply. Your first link covers by point 1 & 2 which I already managed to do it. Second link, I'm concerned there will be lots of manual work (unless its the only option) as I have about 20+ dimentions. However, I will give it a go to see how this bookmarks works. Thanks for sharing.

 

@Greg_Deckler  - wondering if you had chance to look at sample data. Any help would be really helpful. Thanks you in advance for helping. 

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!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!