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

[v-jayw-msft · Accepted solution]

Hi @UBComma ,

Please check the formula:

Table 2 = 
var _2Y35 = SELECTCOLUMNS('Table',"Data[Fall year]",'Table'[Data[Fall Year]]],"Total UG",'Table'[[Total UG]]],"Scenario","[2Y+35NotCL]","Bed Count",'Table'[[2Y+35NotCL]]])
var _2Y50 = SELECTCOLUMNS('Table',"Data[Fall year]",'Table'[Data[Fall Year]]],"Total UG",'Table'[[Total UG]]],"Scenario","[2Y+50NotCL]","Bed Count",'Table'[[2Y+50NotCL]]])
VAR _FY35 = SELECTCOLUMNS('Table',"Data[Fall year]",'Table'[Data[Fall Year]]],"Total UG",'Table'[[Total UG]]],"Scenario","[FY+35NotCL]","Bed Count",'Table'[[FY+35NotCL]]])
VAR _FY50 = SELECTCOLUMNS('Table',"Data[Fall year]",'Table'[Data[Fall Year]]],"Total UG",'Table'[[Total UG]]],"Scenario","[FY+50NotCL]","Bed Count",'Table'[[FY+50NotCL]]])
return
UNION(_2Y35,_2Y50,_FY35,_FY50)

Best Regards,

Jay

[UBComma]

@v-jayw-msft thank you, this is a very elegant solution

[amitchandak]

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

@amitchandak thank you, that is super helpful