Forum Discussion
DAX create a table pivot like
I found a way in the end :
TransformedTable =
VAR __T1 =ADDCOLUMNS(
SUMMARIZE(Test,'Test'[Business Field],'Test'[SD Region BF]),
"Type", "Sales1",
"2023" , CALCULATE( MAX('Test'[Sales1]) , TREATAS( {2023},Test[YEAR])),
"2024" , CALCULATE( MAX('Test'[Sales1]) , TREATAS( {2024},Test[YEAR])),
"2025" , CALCULATE( MAX('Test'[Sales1]) , TREATAS( {2025},Test[YEAR]))
)
VAR __T2 =ADDCOLUMNS(
SUMMARIZE(Test,'Test'[Business Field],'Test'[SD Region BF]),
"Type", "Sales2",
"2023" , CALCULATE( MAX('Test'[Sales2]) , TREATAS( {2023},Test[YEAR])),
"2024" , CALCULATE( MAX('Test'[Sales2]) , TREATAS( {2024},Test[YEAR])),
"2025" , CALCULATE( MAX('Test'[Sales2]) , TREATAS( {2025},Test[YEAR]))
)
VAR __T2 =ADDCOLUMNS(
SUMMARIZE(Test,'Test'[Business Field],'Test'[SD Region BF]),
"Type", "Sales3",
"2023" , CALCULATE( MAX('Test'[Sales3]) , TREATAS( {2023},Test[YEAR])),
"2024" , CALCULATE( MAX('Test'[Sales3]) , TREATAS( {2024},Test[YEAR])),
"2025" , CALCULATE( MAX('Test'[Sales3]) , TREATAS( {2025},Test[YEAR]))
)
RETURN
UNION(__T1,__T2, __T3)
The only issue now is that I need to hard code the year in my DAX... is there any clever way to treat Year in a list and use it there ?
Many thanks,
BR,