Power BI Blog: More on DAX Query View

Welcome back to this week’s edition of the Power BI blog series.  This week, we look in more detail at the new DAX query view feature in Power BI Desktop.

The DAX query view provides a brand new fourth view in public Preview to Power BI Desktop.

The DAX query view gives you the ability to write, edit and see the results of Data Analysis eXpressions or DAX queries on your semantic model.  This means you may now take advantage of the existing DAX queries syntax while working with your semantic model without leaving Power BI Desktop.

As this feature is in public Preview, to see the DAX query view in Power BI Desktop, you need to make sure you are using at least the November 2023 release and have initialised it via File -> Options and Settings -> Options -> Preview features and then clicked the check box next to ‘DAX query view’.

This new way to interact with your semantic model in Power BI Desktop comes with several ways to help you be more productive with DAX queries:

• Quick queries from the Data pane makes it easy to create a DAX query.  You may preview data or show summary statistics to help you understand the data without needing to create visuals or writing a DAX query.  You can find quick queries in the context menu of tables, columns or measures in the Data pane of the DAX query view
• DirectQuery model authors can use DAX query view.  You no longer need to go back to Power Query to preview the data
• A new measure authoring workflow.  You may now see multiple measures at once in the editor, make changes, run the query to test and finally update the model all in one location
• See the DAX query of the visuals.  If a particular Power BI visual is not showing the data you expect, you may now investigate further by looking at the DAX query used by the visual to get data.  This may be accessed from the ‘Performance Analyzer’ pane
• Create your own DAX query.  You may simply write the DAX query in the DAX query view and click run.  You can even define and use measures and variables for that DAX query that do not exist in the model.

DAX query view, as the name suggests, allows you to create DAX queries.  This is different to the DAX formulae used to create measures and calculated columns.  A DAX query is like an SQL query in that you can use it to view data in your model.

With this borne in mind, there are two main parts to a DAX query:

1. EVALUATE: this is required and specifies what data you wish to see
2. DEFINE: this is optional and may specify a measure or named DAX formula to use in the DAX query.  This measure may already be in the model (it might not be).  If it already exists, you can make changes that only apply to this DAX query to try them out.  You can also have the option to update the model with these measures (see below).

The result of running a DAX query is a table of data.  

Some of you may already be familiar with DAX queries from using DAX Studio.  DAX Studio is a community driven and free external tool that can also run DAX queries.  More than just run DAX queries, it has plenty of features utilising DAX authoring / performance.  It may be accessed in Power BI Desktop from the External tools Ribbon once installed.

DAX query view can generate examples for you so that you may see a DAX query, run it,and modify it as needed.  Here’s an example using Quick queries.

Microsoft provide an examples, so that you may follow along.  You may download the Store Sales PBIX from https://learn.microsoft.com/power-bi/create-reports/sample-datasets#updated-samples.

When you first click on DAX query view, a sample query is shown to get the top 100 rows of one of the tables in the model.  In the case of Store Sales, this is the Store table.

Click Run and the top 100 rows of the table are shown in the result grid.  In SQL, this is the same as:

This is great to get a preview of the data for all columns, but it’s difficult to change which columns you might wish to see.  Therefore, let’s try ‘Quick queries’ instead.  In the Data pane, right-click the Store table and from the context menu, choose Quick queries -> Show top 100 rows.

A new query tab will be created with a different DAX query showing the same data.  This time, all the columns are explicitly listed.  There is a TOPN section which also specifies which column and the order in that column to choose the top 100 rows, as well as an ORDER BY to specify the result order.

We may now see how SELECTCOLUMNS works to get data from the model.  In SQL, this would be the same as:

With this quick query we can remove or comment out columns we don’t want to see in the result grid, adjust the number of rows, change the order by column, etc.  SELECTCOLUMNS is used for this query because if you have multiple rows with the same values, they will all show.  You may change this to SUMMARIZE to de-duplicate the rows.

Let’s just look at the City, Store Name, Store Type and Selling Area Size for each location and order by the Selling Area Size for all rows.  To do this, comment out or remove the unwanted columns, change TOPN to simply refer to the table and change the column used in ORDER BY.

Now, we see targeted information about all 104 stores.  We may even copy this and paste the results into Excel.

Now, let’s say we’re curious to see what the possible Selling Area Sizes values are.  This looks like it’s not an exact number, but instead a way to group stores by size.  In the Data pane, right-click the SellingAreaSize column and from the context menu choose Quick queries -> Show data preview.

We can now see there are nine [9] values for Selling Area Size.  As suspected, this is a way to group stores by size.

In SQL, this DAX query is the same as:

Let’s consider how many stores we have by each Selling Area Size.  In this data, there is a measure called [Store Count], so let’s see that number by using a quick query.  It’s easier to find all the measures in the model by changing to Model in the Data pane, or using the Search bar if you already know the name.  In the Data pane, right-click the Store Count measure and from the context menu choose Quick queries -> Evaluate.

This will create a DAX query again in a new query tab.  Yet again, we note that there are 104 stores in this data.

In SQL, there is no real equivalent to a measure in a semantic model: you have to define the aggregation in each SQL query, which is instead the same as an implicit measure in a DAX query.  However, you can get the same result with this SQL query:

This quick query uses SUMMARIZECOLUMNS, which means we can add in a group by column, such as Selling Area Size, to answer the question about how many stores we have for each store size.

You’ll notice most of the stores are comparatively small.  We can build on this query even further, not only by adding in more group by columns, but also by adding in more measures.  Let’s add in Sales.

The DAX query view can also show the DAX formula of the [TotalSales] measure.  We may hover over it to see it in an overlay:

We can see that it’s referencing other measures in the model.  However, we can’t see their DAX formulae in the overlay, but DAX query view can take advantage of the DEFINE syntax in DAX queries.  We may show this measure’s DAX formula and all referenced measures’ DAX formulae in just a couple of clicks:

• Click on the measure name, placing the cursor in the measure name on line 5.  A lightbulb will appear to the left
• click on the lightbulb to see the actions available or use CTRL + . (period)
• click on Define with references.

This will create the DEFINE block for this DAX query just above the EVALUATE.  These won’t be available if you already have a DEFINE command in the query tab.

Not only can you see the DAX formulae, you may even edit one or more of them.  When you run the DAX query, they will use the modified version in the query tab over the model measure DAX formula.  This way we can test any changes.  Here, we have doubled one of the measures.  We may even add a measure to use in the DAX query that doesn’t yet exist in the model, such as to see what the average sales per store is for each store size.

DAX query view can detect when you have changed the DAX formula in a measure that exists in the model, so a clickable superscript appears, called a CodeLens, which will update the model with the new DAX formula if clicked.  For a measure that doesn’t already exist in the model, the CodeLens will add this measure to the model when clicked.  We don’t want to keep the multiply by two [2] change, but we do want to add in the average sales per store measure.  The measure is added to the model and the CodeLens disappears.

We may even remove the DEFINE block and run the query again.

The larger selling area size of the store does show higher average sales.  The measure quick queries and CodeLenstogether create a new measure authoring workflow in DAX query view.

The quick queries for measures also has the option to define all the measures in a table or model.  In the Data pane, right-click the any measure and from the context menu choose Quick queries -> Define all measures in this model.

You now have a large DAX query that defines all the measures and creates an EVALUATE block to see them all at the model level.

SQL again doesn’t have an equivalent to measures in the semantic model.  These would all need to be aggregations in a SQL query, but DAX formulae can reference other measures and perform context changing (looking at last year or by a particular filter) which is more challenging to reproduce in SQL.

Once we have all our measures in one single query tab, we can do things such as “find”.  We can see how many measures use the Selling Area Size column, as an example.  To do this, we click the ‘Find’ Ribbon button or use CTRL + F.  You will note there are two [2] measures that are using that column:

Now we have a feel for how DAX queries work, we may create our own.  To start, let’s add a new query tab.  We can use SUMMARIZECOLUMNS to see how gross margin compares across item categories and then define our own measure to show the year over year difference and order the results by categories that improved the most.

We may quickly do this analysis simply by using DAX queries.  This DAX query doesn’t look as pretty as the Quick queries, as we typed it by hand not paying attention to the formatting.  However, we may click the ‘Format query’ Ribbon button or right-click and choose ‘Format document’ or even use SHIFT + ALT + F to format our DAX query.

Formatting is more than just making it pretty looking and easier to read.  We can collapse blocks where they are indented too.

The visuals themselves are also more than just pretty data visualisations.  The report view visuals get the data from the model with a DAX query.  It is possible to see the DAX query in DAX query view as well.  In Report view, go to the ‘Optimize’ Ribbon and click ‘Performance Analyzer’.  Now, click ‘Start recording’ followed by ‘Refresh visuals’.  Finally, expand the visual title in the list and click ‘Run with DAX query view’.  This will move you to DAX query view where you see the visual DAX query and the results.

In the meantime, please remember we offer training in Power BI which you can find out more about here.  If you wish to catch up on past articles, you can find all of our past Power BI blogs here.