Skip to main content

Microsoft Power BI ‑blogi

Deep dive into DAX query view and writing DAX queries

Headshot of article author Zoe Douglas

Product Manager
30 marraskuuta, 2023

In November 2023 release we added a new fourth view in public preview to Power BI Desktop, the DAX query view!

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. Finally, you can 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 DAX query view in Power BI Desktop, you need to make sure you are using at least the November 2023 release and have gone to File > Options and Settings > Options > Preview features and clicked the check box next to DAX query view.

This powerful new way to interact with your semantic model in Power BI Desktop comes with several ways to help you be as productive as possible with DAX queries.

  • Quick queries from the Data pane makes it easy to create a DAX query. Preview data or show summary statistics to help you understand the data without needing to create visuals or writing a DAX query. Find quick queries in the context menu of tables, columns, or measures in the Data pane of DAX query view.
  • DirectQuery model authors can use DAX query view. No longer do you need to go back to Power Query to preview the data.
  • A new measure authoring workflow. Finally, see multiple measures at once in the editor, make changes, run the query to test, and finally update the model. All in one place!
  • See the DAX query of the visuals. Visual not showing the data you expect? Now you can investigate further by looking at the DAX query used by the visual to get data. Access this from the Performance Analyzer pane.
  • Create your own DAX query. 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 than the DAX formulas used to create measures and calculated columns.  A DAX query is like a SQL query in that you can use it to view data in your model.

There are two main parts to a DAX query:

  • EVALUATE , which is required, that specifies what data you want to see.
  • DEFINE , which is optional, that can specify a measure, or named DAX formula, to use in the DAX query. This measure can already be in the model or not. 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, which we will get into more detail later in this blog post.

The result of running a DAX query is a table of data. You can learn more about DAX queries at aka.ms/dax-queries.

Many of you may already be familiar with DAX queries from using the amazing 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 is feature-rich with DAX authoring/performance features and can be accessed in Power BI Desktop from the External tools ribbon when installed. Thank you to Darren Gosbell and the Power BI Community for your continued advocacy of DAX queries with DAX Studio.

If you are not quite sure where to start with DAX queries, that is ok! DAX query view can generate some for you to see a DAX query, run it, and modify it as needed. Let’s take a look at an example of using Quick queries.

To follow along, download the Store Sales PBIX from https://learn.microsoft.com/power-bi/create-reports/sample-datasets#updated-samples.

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

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

SELECT TOP 100 * FROM Store

This is great to get a preview of the data for all columns, but it’s difficult to change which columns I want to see. So, 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.

So now we can see how SELECTCOLUMNS works to get data from the model.

In SQL this would be the same as:

SELECT TOP 100
store.[LocationID],
store.[City Name],
store.[Territory],
store.[PostalCode],
store.[OpenDate],
store.[SellingAreaSize],
store.[DistrictName],
store.[Name],
store.[StoreNumberName],
store.[StoreNumber],
store.[City],
store.[Chain],
store.[DM],
store.[DM_Pic],
store.[DistrictID],
store.[Open Year],
store.[Store Type],
store.[Open Month No],
store.[Open Month]
FROM
store
ORDER BY
store.[LocationID] ASC

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. 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 that I comment out or remove the unwanted columns, change TOPN to simply refer to the table, and change the column used in ORDER BY.

Now I see targeted information about all 104 stores. I can even copy this and paste the results into Excel.

Now I am 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.

Now I can see there are 9 values for Selling Area Size. As I suspected, this is a way to group stores by size.

In SQL, this DAX query is the same as:

SELECT DISTINCT Store.SellingAreaSize
FROM Store

I now wonder 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. And we find again 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 DAX query. But you can get the same result with this SQL query:

SELECT
    COUNT(*) AS 'Store Count'
FROM Store

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.

And I find most of the stores are comparatively small. I 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. I can hover over it to see it in an overlay:

But I can see that it’s referencing other measures in the model. And I can’t see their DAX formulas in the overlay, but DAX query view can take advantage of the DEFINE syntax in DAX queries. I can show this measure’s DAX formula and all referenced measure’s DAX formulas in a couple clicks.

  1. Click on the measure name, placing the cursor in the measure name on line 5.
  2. A lightbulb appears to the left.
  3. Click on the lightbulb to see the actions available or use CTRL+. (period).
  4. 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 in the query tab.

Not only can you see the DAX formulas, but you can 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 I can test any changes! Here I have doubled one of the measures. I can even add a measure to use in the DAX query that doesn’t yet exist in the model, such to see what the average sales per store is for each store size.

DAX query view can detect 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. And for the measure that doesn’t already exist in the model, the CodeLens will add this measure to the model when clicked. I don’t want to keep the multiply by 2 change, but I do want to add in the average sales per store measure.

The measure is added to the model and the CodeLens disappears.

I can 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 CodeLens together 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.

And now you 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 formulas 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 I have all my measures in one single query tab, I can do things such as find.  I can see how many measures use the Selling Area Size column, as an example. To do this I click the Find ribbon button or use CTRL+F. And I see there are two measures that are using that column:

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

This is great! I can quickly do this analysis all by using DAX queries. This DAX query doesn’t look as pretty as the quick queries, as I typed it by hand not paying attention to the formatting. Thankfully, I can click the Format query ribbon button, or right-click and choose Format document, or even use SHIFT+ALT+F to format my DAX query.

And formatting is more than just making it pretty looking and easier to read. I can collapse blocks by where they are indented.

And visuals themselves are also more than just pretty data visualizations. 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 Optimize ribbon and click Performance Analyzer. Now, click Start recording then 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.

So, there you have it, a quick tutorial on how you can get started using the DAX query view! Here are some more resources to keep you going: