Power BI DAX: UNPIVOT Columns with DAX Expression
For easy analysis we need tabular table compared to crosstab table. In the example “Financial Report” table we may want all sales related values reorganized into one “value” column with an additional “attribute” column for the all column headers of these sales values.
We may use power BI power query to unpivot the “Financial Report” table for the data transformation with few clicks. This is a simple and effective way for most scenarios.
But for some known scenarios, such as when working on direct query dataset or unpivoting columns from larger sharepoint list items, etc. In power BI desktop we may face unsupported power query features or reported performances issues. For such situations we have to consider an alternative unpivot way by calculating columns via DAX functions
In our example my suggested DAX formula to unpivot “Financial Report” table could be as:
Name the unpivoted table as “Financial Report Unpivot by DAX”
Use SelectColumns statement to generate table 1 which keeps all other columns along with, such as Date, Month Name and Month number, Year, Title, country, Product and discount band.
,returns all values in the Sales price column we wish to unpivot into the new column “Value”, and label the column header “Sales Price” to the sale price value in the new column “Attribute”.
Copy the formular statement of Variable _Table 1 and paste it as variable _table2 which returns the values and attributes for unpivoted column “Gross Sales”
Repeate the above steps to generate _table 3 to _table 8 for unpivoted columns Manufacturing Price, COGS, Profit, OData_Sales, Discounts and unit sold.
Define a variable _UNIONTABLE to use UNION function to combine all above 8 stated tables via appending approach
Then Return a final Unpivoted table which has filtered out the row with empty value.
By this DAX unpivoting we reached same table result as we have from power query unpivoted table.
Thanks for your watching and if you like to watch more videos from my channel please press subscribe button. See you in next video.
PBI file from the video:
https://drive.google.com/file/d/1t0R2...
References:
powerbi - Ways to work around DirectQuery in Power BI - Stack Overflow
https://stackoverflow.com/questions/6...
DAX Unpivot - Microsoft Power BI Community
https://community.powerbi.com/t5/Quic...
SELECTCOLUMNS function (DAX) - DAX | Microsoft Docs
https://docs.microsoft.com/en-us/dax/...
UNION function (DAX) - DAX | Microsoft Docs
https://docs.microsoft.com/en-us/dax/...
FILTER function (DAX) - DAX | Microsoft Docs
https://docs.microsoft.com/en-us/dax/...
For more info of my BI & Analytics, please refer to the following website links:
My BI & Analytics Website:
https://sites.google.com/view/mikegua...
My blog website :
https://mikeyubianalytics.blogspot.com/
