Last week, we DSAU25 were introduced to Power BI and Power Query, and this week, we were introduced to DAX in Power BI. DAX, the formula language in Power BI, serves as the powerhouse behind its front-end functionalities. Distinct from Tableau, DAX presents a unique approach to functions and syntax. With DAX, we can create calculated columns, measures, and even calculated tables, which comes handy. I found it quite interesting to use as it has its own way of doing calculations. I found it definitely better than M code which serves the backend purpose as seen in query editor. Through my experience, I’ve come to realize that the calculated columns are good for filtering, while calculated measures shine in aggregating values. Armed with just a day and a half of DAX learning, we were faced with the challenge of synthesizing our newfound knowledge and crafting our first ever report in Power BI.

Data Collection

I began with Adventure Works dataset which can be found here in Kaggle too. There were seven excel files which were downloaded and ready for next step.

Data Cleaning/Wrangling

It is essential to ensure the integrity and format of the data. Utilizing Power BI’s Transform tool, I ventured into the Power Query Editor to meticulously assess the data quality. Although the dataset appears relatively clean, as a diligent data analyst, I conducted a thorough examination to verify its suitability for analysis. I also created a custom table for date/calendar using DAX for proper sorting of other tables.

Data Modeling

With a clean dataset loaded in Power BI, I proceed to model the data within Power BI’s canvas, adhering to the principles of a star schema. That is, establishing the one-to-many relationship between the fact table, which in our case is the Sales table, and dimension tables such as product, region, reseller, salesperson, etc.

STAR SCHEMA in Power BI by prerana amatya

Exploration and Calculated Measures

With the data model in place, I embark on the exploration phase of the dataset. With the help of DAX functions and calculations, I calculated the key performance indicators (KPIs) such as total profit, sales, orders, and quantities which you can say can act as the base calculation for me to further build complex calculations later on. These measures are also called explicit measure.

It is always a good idea to create explicit measures than implicit measure because implicit measure (which is created when one drags it into visualization panel) will be used only in a specific visualization whereas explicit measures can be called upon anywhere else. I also organized all my measures calculations giving them descriptive names, in a separate table just for measures. This is also one of the good practice in Power BI.

Time Intelligence Functions

Next, I also got to use these base measures for my % change from the previous year for the profit, sales, and order. As you can see in the table below, my DAX calculation is working. For this, I used the calculate function along with intermediate to advanced functions like time intelligence function such as Previousyear function. I grouped all these calcs into KPI cards.

Example: Previous Year Profit = CALCULATE([$ Total Profit], PREVIOUSYEAR(DateTable_DAX[Date]))

Iterator Functions

Next up, I wanted to utilize iterator function, I used Averagex to calculate profit in each row. I had sales and cost columns but instead of using calculated columns which is static, we have to use a measure with an iterator here. This gives me average profit which can be sliced by any dimension I choose. See, how using calculated measure can be flexible here.

Example: Average Profit = AVERAGEX(Sales, Sales[Sales]-Sales[Cost])

Modifying Context/Filters

There are some DAX functions we can use to modify context, filter or remove filters such as FILTER, REMOVEFILTERS, ALL, ALLEXCEPT, etc. For our use case, however, simple operator like <>, <= was enough for now which I used to visualize the average profit by subcategory but excluding those from “Bikes” Category.

Example: Average profit subcategory = CALCULATE([Average Profit], ‘Product'[Category]<> “Bikes”)

Visualization and Building Report

Lastly, I brought all my visualization and analysis to build a neat dashboard. Grouping main KPI value with their percentage change, monthly timeline into a summary card, and bringing the average profit by category and subcategory, my report came to life. I also added slicers for years and date to make report dynamic. One can also click on category and subcategory to filter their view. I also tried bookmark button and reset filter button to apply what I had learnt last week which was successful.

POWER BI report from Prerana Amatya on adventure works

 

 

This challenge encapsulates my endeavor to apply our two days of learning DAX, Power Query, and Power BI within a mere two hours. Despite its constraints, I view it as a stepping stone towards crafting more advanced dashboards in Power BI soon including relationship functions, parameters, etc.

#powerbi #dax

Prerana Amatya
Author: Prerana Amatya