Get our whitepaper on form submission right to your inbox!!!
When I was trying to do some data modeling in PowerBI, and I was curious to experiment something new.
Usually when we need to pivot data in a table by a particular column we use Power Query to select necessary columns & build pivot tables. But I wanted to do it in a different way by using DAX.
After several attempts, I successfully did it & finish my data modeling.
Since it does in DAX, we can use this as an optimization method for data loading. Generally, when we use do too many data manipulations in Power Query mode, it will consume time to get data loaded.
Below is my raw data. It is Sales statistics of three sales people. I have a “Date” column, “Sales” quantity and the name of “Sales Person” in line.
I want to pivot my data by the “Sales Person” column below.
I use the formulas below, to create a new table, which is a pivot of above.
I select “New Table”
And on the formula bar, I use a combination of below functions to achieve the same.
AddColumns > Helps me to create a table with columns from a different table
Summarize > To summarize the “Date” column, by removing duplicates
Calculate > To obtain the sales amount by the given sales person.
Below is how I organize my DAX to achieve the results