DAX is simple but works on the mantra of “Nothing worth having comes easy”. Anything worthwhile requires effort, so don’t give up if you’re up against it. This blog will come to your rescue and help you learn some DAX functions in depth, allowing you to breathe a sigh of relief. It is hard to understand the difference and usability of the DAX functions when you’re practicing on large data sets like Adventure Works. So, we have developed a relatively simple data set with a very few rows. The data set consists of two tables, connected by the Product ID column. The Product table contains 4 products (cellphones).
The Sales table contains the quantity of different products sold. Notice that some Product IDs (5 and 6) exist in the Sales Quantity but these products do not exist in the Product table.
To understand the behavior of different DAX functions for the same column in the data set:
- Right click on the Products
Since the data set is very small and this exercise is purely focused on understanding the DAX functions, we will not be creating a separate table for measures.
- Click on New Measure.
Measure tools open.
- Type the following DAX expression in the Formula bar and press Enter.
In this expression:
- The DISTINCT() function returns the distinct products in a virtual table.
- COUNTROWS() counts the rows in the virtual table.
- Right click on the Products
- Click on New Measure.
Measure tools open.
- Type the following DAX expression in the Formula bar and press Enter.
In this expression:
- The VALUES() function returns the distinct products in a virtual table.
- COUNTROWS() counts the rows in the virtual table.
- Right click on the Products
- Click on New Measure.
Measure tools open.
- Type the following DAX expression in the Formula bar and press Enter.
In this expression:
- The ALL() function returns the distinct products in a virtual table.
- COUNTROWS() counts the rows in the virtual table.
- Right click on the Products
- Click on New Measure.
Measure tools open.
- Type the following DAX expression in the Formula bar and press Enter.
In this expression:
- The ALLNONBLANK() function returns the distinct products in a virtual table.
- COUNTROWS() counts the rows in the virtual table.
- Click on the matrix visual in the visualization pane.
- Drag and drop Product from the Products table to the rows.
- Drag and drop Count Rows using DISTINCT (CM) from the Products table to the Values.
- Drag and drop Count Rows using VALUES (CM) from the Products table to the Values.
- Drag and drop Count Rows using ALL (CM) from the Products table to the Values.
- Drag and drop Count Rows using ALLNONBLANK (CM) from the Products table to the Values.
Now that you have used all the different functions in separate measures, lets analyze the results.
- The blank row in the Product Name indicates the invalid relationship created by the Product ID 5 and 6.
- Count Rows using DISTINCT (CM) counts the Distinct products and shows the results based on the incoming filter context.
- Another important consideration at this point is that DISTINCT() does not count the blank value and the total distinct products is 4.
- Count Rows using VALUES (CM) counts the Distinct products and shows the results based on the incoming filter context.
- VALUES() counts the blank value and the total distinct products is 5.
- Count Rows using ALL (CM) counts the Distinct products and shows the results ignoring the incoming filter context.
- ALL() counts the blank value and the total distinct products is 5 (Which is same for all rows and totals).
- Count Rows using ALLNONBLANKROW (CM) counts the Distinct products and shows the results ignoring the incoming filter context.
- ALLNONBLANK() does not count the blank value and the total distinct products is 4 (Which is same for all rows and totals).
Conclusion:
DAX is a formula language developed by Microsoft for data enrichment. The results of a calculation depend heavily on the DAX function used. Although some DAX functions look similar, but they produce different results because they work on a different mechanism. It is even difficult to interpret their functionality if you’re working on a large data set. In this blog post, VALUES(), DISTINCT(), ALL() and ALLNOBLANKROW() have been discussed using a small data set for clear understanding of the concept.
Are you looking for an instructor-led Power BI or Power Apps training in Australia? We provide classroom as well as online live trainings for the following courses:
