Yes, you can achieve this transformation using DAX. However, please note that DAX is not as flexible as Power Query when it comes to reshaping data. Here’s how you can do it:
First, you need to create two separate tables for each category of sales. You can do this using the SUMMARIZE function in DAX:
Category1Sales =
SUMMARIZE(
FILTER(YourTable, YourTable[Year] >= MIN(YearSlicer[Year]) && YourTable[Year] <= MAX(YearSlicer[Year])),
"ID", YourTable[ID],
"Measure", "Category 1 Sales",
"Year", YourTable[Year],
"Sales", SUM(YourTable[Category 1 Sales])
)
Category2Sales =
SUMMARIZE(
FILTER(YourTable, YourTable[Year] >= MIN(YearSlicer[Year]) && YourTable[Year] <= MAX(YearSlicer[Year])),
"ID", YourTable[ID],
"Measure", "Category 2 Sales",
"Year", YourTable[Year],
"Sales", SUM(YourTable[Category 2 Sales])
)Then, you can union these two tables together:
FinalTable = UNION(Category1Sales, Category2Sales)
This will give you a table with the columns “ID”, “Measure”, “Year”, and “Sales”. You can then use this table to create a matrix visual in Power BI, with “ID” and “Measure” on the rows, “Year” on the columns, and “Sales” in the values.
Please replace YourTable and YearSlicer with the actual names of your table and slicer. Also, ensure that your slicer is connected to a table that contains all the possible years, and that this table is related to your main table.
Remember, DAX operates on tables and columns, not on individual cells or rows. Therefore, transformations that require cell-by-cell operations are often more complex in DAX than i...1.
