Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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 s...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

 https://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's avatar
    Anonymous
    Not applicable
    6 years ago

    Adding to above i cant use drill down and many to many relationship 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi 

     

    i have solved issue 1.

     

    issue 2 and 3 remains

    • amitchandak's avatar
      amitchandak
      Icon for Super User rankSuper User
      6 years ago

      For Issue two. Create a sort column. That should depend on if is the month, year or QTR.

       

      For the month it should be in format YYYYMM. Mark that as sort column

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi

         

        this works when the month name is not pivoted. Once we pivot it doesnt

         

        As per the image below i put the value field in the X axis, that time year and quarter is sorted but month is sorted by name

         

         

        Value column created after pivot