Forum Discussion

UBComma's avatar
UBComma
Icon for Helper III rankHelper III
4 years ago
Solved

Unpivot Columns of Calculated Table

I have reviewed posts with a similar problem but I'm still having trouble unpivoting select columns from a calculated table. The table looks like this:

Data[Fall Year][Total UG][FY+50NotCL][FY+35NotCL][2Y+50NotCL][2Y+35NotCL]
2013178423652580374
2012174693063569889
2014178693081996267
202119629589511377160
2015181193072189564
20161855538225100077
201819709376401304135
201920132486451263122
201719103423441210118
20202035513411191971231

 

and I need a table that looks like the sample below, and would be SO EASY if I could use Power Query. Could someone help me with the DAX to do this? (Sample of top 20 rows)


Data[Fall Year][Total UG]ScenarioBed Count
201317842[FY+50NotCL]365
201317842[FY+35NotCL]25
201317842[2Y+50NotCL]803
201317842[2Y+35NotCL]74
201217469[FY+50NotCL]306
201217469[FY+35NotCL]35
201217469[2Y+50NotCL]698
201217469[2Y+35NotCL]89
201417869[FY+50NotCL]308
201417869[FY+35NotCL]19
201417869[2Y+50NotCL]962
201417869[2Y+35NotCL]67
202119629[FY+50NotCL]589
202119629[FY+35NotCL]51
202119629[2Y+50NotCL]1377
202119629[2Y+35NotCL]160
201518119[FY+50NotCL]307
201518119[FY+35NotCL]21
201518119[2Y+50NotCL]895
201518119[2Y+35NotCL]64
 
 
Need Help
Need Help DAX

6 Replies

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

    UBComma , A new table like

     

    Union (
    Summarize( Data, Data[Fall Year], "Data", [Total UG], "Scenario", "FY+50NotCL", "Value", Sum(Data[FY+50NotCL])),
    Summarize( Data, Data[Fall Year], "Data", [Total UG], "Scenario", "FY+35NotCL", "Value", Sum(Data[FY+35NotCL])),
    Summarize( Data, Data[Fall Year], "Data", [Total UG], "Scenario", "2FY+50NotCL", "Value", Sum(Data[2FY+50NotCL])) ,
    Summarize( Data, Data[Fall Year], "Data", [Total UG], "Scenario", "2FY+35NotCL", "Value", Sum(Data[2FY+35NotCL]))
    )

     

     

    you can use selectcolumns in place of summarize

    https://docs.microsoft.com/en-us/dax/selectcolumns-function-dax

    https://radacad.com/create-a-subset-of-the-table-in-power-bi-and-add-calculations-using-selectcolumns-dax-function

     

    Also refer

    https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

    • niels_vngi's avatar
      niels_vngi
      Frequent Visitor
      4 years ago

      Hi all,

       

      Just wanted to say that this topic was of great help! I was facing a similair issue, took me quite a while to find this post but then the issue was quickly resolved.

       

      Thanks for all the input! 👌💪👍

       

      Best,

       

      Niels

    • ML222's avatar
      ML222
      Frequent Visitor
      3 years ago

      Is there a way to create a variable for the repeated columns that you use on each line in the UNION? To make the code easier to read

      i.e.
      VAR StaticValues = Data, Data[Fall Year], "Data", [Total UG],

    • issya's avatar
      issya
      New Member
      1 year ago

      this is really helpful! is there a way to first groupby a big table to get the initial dataset and then do this process to get the final output. Thanks!

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    HI UBComma 
    Here is sample file with the solution https://www.dropbox.com/t/Ya51y92MbC5Y8Wwd

    Unpivotted Table = 
UNION (
    SUMMARIZE ( Data, Data[Fall Year], "Total UG", MAX ( Data[Total UG] ), "Scenario", "[FY+50NotCL]", "Bed Count", MAX ( Data[FY+50NotCL] ) ),
    SUMMARIZE ( Data, Data[Fall Year], "Total UG", MAX ( Data[Total UG] ), "Scenario", "[FY+35NotCL]", "Bed Count", MAX ( Data[FY+35NotCL] ) ),
    SUMMARIZE ( Data, Data[Fall Year], "Total UG", MAX ( Data[Total UG] ), "Scenario", "[2Y+50NotCL]", "Bed Count", MAX ( Data[2Y+50NotCL] ) ),
    SUMMARIZE ( Data, Data[Fall Year], "Total UG", MAX ( Data[Total UG] ), "Scenario", "[2Y+35NotCL]", "Bed Count", MAX ( Data[2Y+35NotCL] ) )
)

     

  • UBComma's avatar
    UBComma
    Icon for Helper III rankHelper III
    4 years ago

    tamerj1 & amitchandak Thank you both very much. I was trying a similar approach to solve this but I couldn't get the syntax right. This is really helpful