Create calculated tables using DAX in Excel

Tutorials \ Create calculated tables using DAX in Excel

For those that have used Power BI extensively, you would know that Data Analysis Expression (DAX) is a powerful tool that allows you calculate all kinds of fields, measures and tables.

Much of this functionality is also available in Excel via the Power Pivot functionality.

However, while Power BI has a very nice front-end to allow you to use this functionality, Excel instead opted to bury it in a completely separate front-end that is both outdated and difficult to use.

When it comes to using the more powerful table functions like the SUMMARIZE or DATATABLE function to create calculated tables, it’s even more hidden.

This article will give provide the steps needed to use those functions.

Understanding the link between Power Query and Power Pivot’s Data Model.

Power Query provides the means to get data from multiple of sources. It also controls where to send this data. When you pull data with Power Query, you are given the option to load the data to standard Table, to a Pivot Table and/or to the Data Model.

This option is particularly important if you want to make the most of DAX. Typically, you will want to load the data directly to the data model. Those other options can be useful, however.

By loading the data to a Table, it provides you the ability to edit the link between the Table you see and the Power Query. It is there that Microsoft decided to hide the front-end user interface giving you the ability to create calculated tables using DAX.

Note that these menus are only available if the Table was built with Power Query. It is not available for static tables created directly from Excels “Format as Table” button. And it is also not available if table is not loaded to the data model.

Create a calculated table using DAX from scratch

Creating a calculated table using DAX is easy, once you know the steps.

  1. Create a new sheet in your workbook.
    This sheet is temporary and can be deleted at the end of the process.
  2. Select any cell and go to the Data Tab in the ribbon, and select Get Data \ From Other Sources \ From Table/Range.
  3. Click OK when prompted to select the cell.
  4. In Power Query, immediately select the Close and Load To option.
  5. Ensure the option Table is selected, the option New Worksheet is selected, and the option Add this data to the data model is selected.
    Without the Table option, you cannot access the contextual menu to edit the Table.
    Without the Add this data to the data model, the contextual Edit DAX menu will be greyed out.
  6. Right-click on the newly created table, and choose Table \ Edit DAX.
  7. Change the Command Type dropdown from Table to DAX.
  8. Enter the DAX expression you want starting with the word EVALUATE and click OK.
    For example:
    EVALUATE DataTable(“Name”, STRING, “Region”, STRING ,{{” User1″,”East”},{” User2″,”East”}})
  9. Rename the table and sheet to give it a meaningful name.
    This sheet and table will need to remain in your workbook.
  10. Go to the Power Pivot ribbon tab and click on Add to Data Model.
  11. For clean up, you can now delete the initial sheet you created in Step 1. You can also delete the Power Query you created in Steps 2-4 from the Queries and Connections panel.
    Note that you do not want to delete the resulting table created in Step 5 onwards.

Create a calculated table using DAX from an existing connection

If you already have existing connections in your workbook, you can create a table quickly from one of these existing connections instead.

  1. Go to the Data ribbon tab and select Existing Connections.
  2. Choose one of the connections and click Open.
  3. Ensure the option Table is selected, the option New Worksheet is selected, and the option Add this data to the data model is selected.
    Without the Table option, you cannot access the contextual menu to edit the Table.
    Without the Add this data to the data model, the contextual Edit DAX menu will be greyed out.
  4. Right-click on the newly created table, and choose Table \ Edit DAX.
  5. Change the Command Type dropdown from Table to DAX.
  6. Enter the DAX expression you want starting with the word EVALUATE and click OK.
    For example:
    EVALUATE DataTable(“Name”, STRING, “Region”, STRING ,{{” User1″,”East”},{” User2″,”East”}})
  7. Rename the table and sheet to give it a meaningful name.
    This sheet and table will need to remain in your workbook.
  8. Go to the Power Pivot ribbon tab and click on Add to Data Model.