DAX is a formula language used for creating calculated columns, measures, and tables. It is a common misconception that the table functions in DAX are used to create calculated tables only. However, this is not true. In this blog, you will learn how to use table functions in measures.
To test the table functions in measures:
- Right click on All Measures in the Fields.
- Click on New measure.
Measure tools open.
- Type the following formula in the formula bar and press Enter.
In this Expression:
- You have used VALUES() on the Product table which returns all the rows.
- VALUES() is encapsulated in COUNTROWS() function to return the number of rows.
- Click on the card visual in the visualization pane.
- Drag and drop Testing Table functions in a Measure (CM) from the All Measures table to the Fields.
- Click on the matrix visual in the visualization pane.
- Drag and drop Category from the Products table to the rows.
- Drag and drop Testing Table functions in a Measure (CM) from the All Measures table to the Values.
Notice that as the filter context from the rows propagates, the values for the Testing Table functions in a Measure (CM) change. The total of the card visual and matrix is same.
Let’s now slightly alter the DAX expression and add column to the VALUES() parameter.
- Click on Testing Table functions in a Measure (CM) in All Measures table.
Measure tools open.
- Replace Product table with Product Column so that your expression looks like the image below.
In this expression:
- The VALUES() function finds the distinct values of products.
- The VALUES() forms a virtual table and COUNTROWS() counts the number of rows in the virtual table.
- Click on Testing Table functions in a Measure (CM) in All Measures table.
Measure tools open.
- Replace with Subcategory column so that your expression looks like the image below.
In this expression:
- The VALUES() function finds the distinct values of Product Subcategory.
- The VALUES() forms a virtual table and COUNTROWS() counts the number of rows in the virtual table.
Notice that only 37 categories exist. As the filter context comes into play in the Matrix, it is seen that Accessories have the greatest number of subcategories and Bikes have the least.
- Click on Testing Table functions in a Measure (CM) in All Measures table.
Measure tools open.
- Replace the VALUES() function with ALL() so that your expression looks like the image below.
In this expression:
- The ALL() function finds the distinct values of Products Subcategory.
- The ALL() forms a virtual table and COUNTROWS() counts the number of rows in the virtual table.
Notice the change in values. ALL() ignores the filter context so the same value is shown for all categories and on the card. This is the total number of subcategories in the data.
- Click on Testing Table functions in a Measure (CM) in All Measures table.
Measure tools open.
- Replace the Subcategory column with the Category column so that your expression looks like the image below.
In this expression:
- The ALL() function finds the distinct values of Products Category.
- The ALL() forms a virtual table and COUNTROWS() counts the number of rows in the virtual table.
Notice that only 4 categories exist. Filter context is ignored by ALL().
Conclusion:
DAX is a formula language developed by Microsoft to help data analysts enrich their data model by performing calculations on the top of data model. DAX can be used to create calculated columns, measures, and tables. It is a formula language, and these formulas are used to perform calculations. In this blog post, table functions in DAX are used to calculate measures.
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:
