Forum Discussion
Unpivot Columns of Calculated Table
- 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
Also refer
https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/
- 4 years ago
HI UBComma
Here is sample file with the solution https://www.dropbox.com/t/Ya51y92MbC5Y8WwdUnpivotted 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 , 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
Also refer
https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/
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],