DAX is a formula language. This means that DAX formulas derive the DAX expressions. DAX functions consists of formulas that perform defined calculations and bring about meaningful information from the data. After having developed the basic foundations for DAX in our previous blogs, lets start getting our hands on DAX functions. The data set used for this blog post is Adventure Works DW 2020. It is a sample data model developed by Microsoft to enhance the DAX learning of their users. The Adventure Work dataset represents the data of fictitious bicycle manufacturer that sells bicycles and accessories to global markets.
Adventure Works Model Structure
The model contains seven tables:
Source: https://docs.microsoft.com/en-us/power-bi/guidance/dax-sample-model
This is what your data model looks like once you have loaded the Adventure Works data set into Power BI desktop.
The model does not contain any DAX calculations. So we begin by following the best practice.
BEST PRACTICE TIP: Store all measure in a separate measure table.
1.Click on Home Tab in the Ribbon Menu.
2.Click on Enter Data in the Data Section.
3.Name Column1 to Measures.
4.Name the Table to All Measures.
5. Click on Load.
Notice that a single column table which does not contain any data is created.
SUM()
To create a measure that calculates the SUM:
- Right on All Measures Table in the Fields Pan.
- Click on New Measure.
- Create a Measure with the name ‘Total Sales’ that finds the Sum of Sales Amount Column in the Sales Table.
Notice that just when you start typing, the Intellisense in Power BI’s Formula bar suggests you the Formulas along with their description.
4.Type the following formula in the formula bar.
Once the measure has been created, lets create a simple card visual to check the value.
5.Select the Card Visual from the Visualization Pane.
6.Drag and Drop the Total Sales from All Measures to the Fields.
The value shown in the card visual is the summation of Sales Amount for all transactions in the Sales Table.
Now that we have created a simple measure. Lets move further.
For the next two measures, we will be using the ‘Sales Territory’ table.
COUNT()
To create a measure that calculates the COUNT:
- Right on All Measures Table in the Fields Pan.
- Click on New Measure.
- Create a Measure with the name ‘Count Countries’ that counts the countries in the ‘Sales Territory’ Table.
4.Lets visualize the value stored in the measure using a card visual
DISTINCTCOUNT()
To create a measure that calculates the DISTINCTCOUNT:
- Right on All Measures Table in the Fields Pan.
- Click on New Measure.
- Create a Measure with the name ‘Distinct Count Countries’ that counts the unique countries in the ‘Sales Territory’ Table.
4.Lets visualize the value stored in the measure using a card visual.
Count Vs. Distinct Count:
The measure ‘Count Countries’ shows that a total of 11 countries are present in the Sales Territory table. This value is correct as we have 11 rows and each row contains a value in the Country Column. However, if we want to find the unique counties then the COUNT function will not serve the purpose. For this, we need a function that returns unique values. It can be verified from the data that ‘United States’ is present 4 times. This means that Adventure Works company operates in 7 unique countries.
Conclusion:
DAX is used to bring about some meaningful information hidden inside raw data. This is done using functions in DAX. Functions in DAX perform data manipulation. A function in DAX is a named formula within an expression. In this blogpost, SUM, COUNT and DISTINCTCOUNT have been discussed.
Are you a data analyst and want to learn more about Power BI? Why not sign up for Power BI training in Australia. We provide our services in the following regions
