Unpivot Columns of Calculated Table

[UBComma]

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]
2013	17842	365	25	803	74
2012	17469	306	35	698	89
2014	17869	308	19	962	67
2021	19629	589	51	1377	160
2015	18119	307	21	895	64
2016	18555	382	25	1000	77
2018	19709	376	40	1304	135
2019	20132	486	45	1263	122
2017	19103	423	44	1210	118
2020	20355	1341	119	1971	231

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]	Scenario	Bed Count
2013	17842	[FY+50NotCL]	365
2013	17842	[FY+35NotCL]	25
2013	17842	[2Y+50NotCL]	803
2013	17842	[2Y+35NotCL]	74
2012	17469	[FY+50NotCL]	306
2012	17469	[FY+35NotCL]	35
2012	17469	[2Y+50NotCL]	698
2012	17469	[2Y+35NotCL]	89
2014	17869	[FY+50NotCL]	308
2014	17869	[FY+35NotCL]	19
2014	17869	[2Y+50NotCL]	962
2014	17869	[2Y+35NotCL]	67
2021	19629	[FY+50NotCL]	589
2021	19629	[FY+35NotCL]	51
2021	19629	[2Y+50NotCL]	1377
2021	19629	[2Y+35NotCL]	160
2015	18119	[FY+50NotCL]	307
2015	18119	[FY+35NotCL]	21
2015	18119	[2Y+50NotCL]	895
2015	18119	[2Y+35NotCL]	64

[amitchandak · Accepted solution]

@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-selectcolumn...

Also refer

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

[UBComma]

@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

[tamerj1 · Accepted solution]

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] ) )
)

[amitchandak · Accepted solution]

@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-selectcolumn...

Also refer

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

[issya]

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!

[ML222]

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],

[niels_vngi]

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