Learning DAX opens a whole new world Power BI for you. It upskills the user with a skill which is not only limited to Power BI. DAX can be used with tabular Microsoft products like Microsoft Power Point for Excel and Microsoft Analysis Services. Although there is no doubt that learning DAX makes you a better professional, but the learning curve is steep and DAX language cannot be learnt overnight.

Creating a Calculated Table

Using DAX expressions, you can calculate tables containing values of your choice. This is as easy as creating calculated columns and calculated measures. Calculated tables have all the properties of regular tables in Power BI desktop. Some of the common functions used for creating a calculated table are:

• DISTINT()
• VALUES()
• CROSSJOIN()
• ALL()
• ALLEXCEPT()
• FILTER()
• SUMMARIZE()
• CALNDER()
• CALENDERAUTO()
• UNION()

To create a calculated table:

1. Click on the Modeling tab in the Ribbon.
2. Click on New table.

Using VALUES()

The first table function that we will be introducing to calculate tables is VALUES().

The result of VALUES() depends on the input argument:

• When the input argument is a table, all the rows of the table are returned. Duplicate rows are preserved.
• When the input argument is a column, a single-column table is returned containing distinct values. Duplicates are removed in this case and only unique values are returned.

Values using table name as argument:

To see how VALUES() is used to create tables:

1. Click on the Modeling tab in the Ribbon.
2. Click on New Table.
3. Type the following expression in the formula bar and press Enter.

In this Expression:

• The rows from Product table are stored in a new calculated table.

4. Click on the Data tab in the left Ribbon.
5. Click on Testing Table Functions using VALUES (CT) in the Fields pan.

Values using column name as argument:

Notice that all the rows from the Product table are returned. Now we will what happens when a column is used as an argument in VALUES() function.

6. Click on Testing Table Functions using VALUES (CT) in the Fields pan.

Table tools menu box opens.

7. Replace the Product table with Product Category column and press Enter.

In this Expression:

• The unique product categories from the Product table are found because the argument to VALUES() is s table column.

8. Click on the Data tab in the left Ribbon.
9. Click on Testing Table Functions using VALUES (CT) in the Fields pan.

Notice that only distinct values without any duplicates have been returned in the form of a single column table.

Conclusion:

Calculated tables are created in Power BI using DAX functions. These tables have all the properties similar to the regular tables in Power BI. One of the most commonly used table functions is VALUES(), the output depends on the argument used in the VALUES() function. Both column and tables can be used as arguments for the VALUES(). In this blog post, a test case for each of these arguments to VALUES() is presented.  

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 the following Power BI trainings:

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