Forum Discussion
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
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
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.
For switching Column/Measure: https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/500115
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
