Forum Discussion
Unpivot Duplicates
i need the X acis to dynamically change to Year Qtr and month based on the slicer selection, In the slicer i need to see year, qtr and month so that when i select year the X axis shows year, when i select month its should show month and so on.
Data is like below:
| TNX_ID | Date | Amount |
| 1 | 12/1/2019 | 10 |
| 2 | 12/2/2019 | 15 |
| 3 | 12/3/2019 | 20 |
| 4 | 12/1/2019 | 25 |
| 5 | 12/2/2019 | 30 |
| 6 | 12/3/2019 | 35 |
| 7 | 12/1/2019 | 40 |
| 8 | 12/2/2019 | 45 |
| 9 | 12/3/2019 | 50 |
| 10 | 12/1/2019 | 55 |
I split the date col to year, Quarter and month , then i did Unpivot so that i could use the Attribute in the slicer.
But this is causing duplicate values and the amount and count of transaction is getting doubled.
Also as TNX_ID is unique col i am not able to create a relation ship to any other table.
8 Replies
The information you have provided is not making the problem clear to me. Can you please explain with an example. If possible please share a sample pbix file after removing sensitive information.
But the best way is you create a calendar and join it with your table. Create Month, Qtr and year there.
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.
Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970https://www.dropbox.com/s/zk64ewczk03mbsa/Issue_unpivot.pbix?dl=0
Hi Please find the PBIX file attached in the link above.
Find details Below:
I have on etable called Fact, I have user names the date on which they wer added and the the filed in which they work as three columns.
I need to show a trend chart on the growth of the users getting added to this table on yearly, monthly and quarterly .
the trend chart should be cumulative trend and hence i have created a measure with Max date added in filter.
Issues 1
I need to add a slicer with the values Year, Quarter,Month so that the X axis of the trend chart should change dynamicaly on the slicer selection.
I have created a copy of the table, extracted year, month quarter and then unpivoted them to get Attribute(which i can add in slicer) and Value (to add to the X axis)
Created a bidirectinal relationship also but as per PBIX i am getting wrong trend it is not taking cumaltive
Issue 2:
When i have done Unpivot not able to sort month
Issue 3 :
Need to show cumulative growth percentage also
Anonymous
Alternatively, you may take advantage of Drill down in a visualization in Power BI and Use bookmarks to share insights.
