GUI for DAX queries

Use this trick to test Power BI data without any prior knowledge of DAX.

You know testing Power BI data is easy with CAT. You can test data in a locally open .pbix file. You can test data in senamtic models (datasets) published in a Power BI workspace. But as mentioned in the linked article, those data sources expect DAX, not SQL.

Here we describe step by step how you can easily prepare your queries without any prior DAX knowledge.

We expect that you already have either a Power BI .pbix file open or you have access to a semantic model (dataset) in a Power BI workspace.

Download and Install DAX Studio

If you don’t know any DAX or your knowledge of DAX is limited, we highly recommend to download DAX studio. It is a great tool, even for beginners - it has a built in GUI query builder.

First, download DAX studio from here: https://daxstudio.org.

The installation is easy, just typical next, next, next…

Connect to Your Model

With DAX studio, you can connect to an open .pbix file, to Power BI workspace semantic model (dataset) or to SQL Server Analysis Services (SSAS) instance. Note that for connection to Power BI workspace you need Power BI Premium or Power BI Premium per User.

DAX studio connection dialog

Build Your Query

The DAX studio connects to your model and loads its metadata. You can use it to build your query. No DAX knowledge is needed. Just drag and drop what you need. With DAX studio, it is matter of seconds to create a query.

DAX studio model

  1. Find measures or columns you need for your query.

SUMMARIZECOLUMNS ( ‘DIM AIRLINES’[AIRLINE_NAME], “AVG Delay in Minutes”, [AVG Delay in Minutes] )2. Drag the measures and/or columns to the Columns / Measures pane.

  1. Filter out data you don’t want. Note that it works even with metrics.

  2. Sort the resulting dataset. This is important if you will be doing sets match comparison with data from other model or from other provider (such as ORACLE, SQL server etc.)

  3. Use Update button to build your query (or turn on auto update).

  4. Examine your final query.

You can run the query with Run button (top left corner) and see the results in Results pane (bottom left).

OK, I Have the Query, What Next?

Use the query in your tests, just as if it was a SQL statement. Use PowerBI@1 provider for locally open .pbix file. Use Dax@1 provider for models in Power BI workspaces or in SQL Server Analysis services instances.

See project files documentation if you are not sure how to configure CAT testing project.

← DAX introduction
Test Databricks →