Testing table functions in measures

DAX table functions in measures offer powerful ways to create calculated columns, measures, and tables. At first, many people mistakenly think table functions in DAX apply only to calculated tables. However, this is not true. In fact, you can use table functions effectively within measures. In this blog, you will learn exactly how to use these table functions in measures to improve your data models.

Create a New Measure Using DAX Table Functions in Measures

To begin, to test the table functions in measures:

1. First, right-click on All Measures in the Fields pane.

2. Next, click on New measure.

Measure tools open.

3. Then, type the following formula in the formula bar and press Enter.

   

In this Expression:

• First, you use the VALUES() function on the Product table, which returns all the rows.

• Then, VALUES() is encapsulated in the COUNTROWS() function to return the number of rows.

4. Next, click on the Card visual in the Visualisation pane.

5. Finally, drag and drop Testing Table Functions in a Measure (CM) from the All Measures table to the Fields.
   
6. Click on the matrix visual in the visualization pane.
7. Drag and drop Category from the Products table to the rows.
8. 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. Nevertheless, the total shown in both the card visual and the matrix remains the same.

Let’s now take it a step further by slightly altering the DAX expression and adding a column to the VALUES() parameter.

9. Click on Testing Table functions in a Measure (CM) in All Measures table.

Measure tools open.

10. 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.

11. First, click on Testing Table functions in a Measure (CM) in the All Measures table.

Measure tools open.

12. Next, replace the column with the Subcategory column so that your expression matches 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.

13. Click on Testing Table functions in a Measure (CM) in All Measures table.

Measure tools open.

14. Replace the VALUES() function with ALL() so that your expression looks like the image below.

In this expression:

• The ALL() function identifies the distinct values of the Products Subcategory.

• The ALL() function creates a virtual table, and then COUNTROWS() counts the number of rows in that table.

Notice the change in values. Because the ALL() function ignores the filter context, it shows the same value across all categories and on the card. As a result, this value represents the total number of subcategories in the data.

15. First, click on Testing Table functions in a Measure (CM) in the All Measures table.

Measure tools open.

16. Replace the Subcategory column with the Category column so that your expression matches the image below.

    

In this expression:

• The ALL() function finds the distinct values of the Products Category and creates a virtual table.

• COUNTROWS() then counts the number of rows in this virtual table.

To begin with, notice that only four categories exist. However, the filter context is ignored by the ALL() function.

Conclusion:

DAX is a formula language that Microsoft developed to help data analysts enrich their data models by performing calculations directly on them. Specifically, analysts can use DAX to create calculated columns, measures, and tables. Moreover, since DAX is a formula language, users apply these formulas to carry out various calculations. In this blog post, we will use table functions in DAX to calculate measures.

Are you looking for instructor-led Power BI or Power Apps training in Australia? If so, we offer both classroom and online live training sessions for the following courses:

• Power BI Essentials
• Power BI Advanced
• DAX Essentials
• Power Apps Essentials