Dax Query View in Power BI

The March 2024 update of Power BI brought a new dynamic to Power BI Desktop. A DAX Query view on the left-hand side.

The ability to query data using DAX has existed for a long time. DAX Studio does a first-class job of providing an environment to use queries. We’ve never had an environment to explore and develop queries in Power BI Desktop without changing our data model. That has just changed.

What is a DAX Query?

Queries return data. SQL queries return data from a database using the SQL language. DAX Queries do the same for a Power BI Data model. Unlike SQL Queries, a DAX query cannot change the underlying data. Neither can they add visualisations to a report.

What a query can do is help you understand the data you have. So, if you have imported a large dataset and now need to verify it, then writing some queries is a good way to go.

The new query view is also useful for developing DAX. Imagine that I need to write a complex table expression. I can try it out, get the expression right and perhaps explore variations of it in the query view. Only when I’m happy with the expression do I need to add it to my model.

I’ll explore some simple queries at the end. First, though a few notes about the query view and the inevitable mention of CoPilot.

So where is my DAX Query View?

Although the view is available, you may not see it. Here are two remedies.

  • Check your Power BI version. Do you need updates?
  • The March 2024 update might have made DAX Query visible to most people, but it still retains its status as a preview feature. Check preview feature option in Power BI desktop settings.

CoPilot and the Command Palette

The new DAX query view comes with CoPilot enabled. Microsoft suggests five main areas of CoPilot assistance in DAX Query. These are:

  • Writing DAX Queries
  • Altering Existing Queries
  • Creating new Measures
  • Learning about DAX and DAX Functions
  • Explaining an Existing Query

For a deeper dive, try this entry in the Power BI Blog.

I’ll also mention the command palette here. Which takes its inspiration from Visual Studio Code. Opening the command palette grants access to an extensive menu of editor commands; many of which have keyboard shortcuts. Use it to discover your options for editing expressions.

Consider both Copilot and the Command Palette as assistive technology.

Getting Started

Let’s look at some DAX query examples. To start, we’ll need some data. Open up Power BI Desktop and select, “Use Sample Data”. Next, click on the Load Sample Data button and select financials. You now have a table of data that these examples should work with.

To List all the data in the table use

Evaluate financials

Now click on the Run button in the top left corner of the Dax Query View

You should see a listing of the entire table.

Perhaps not so useful, so let’s first reduce the number of columns to something manageable. Try:

EVALUATE
  SELECTCOLUMNS(financials,
                "Country",financials[Country],
                "Product",financials[Product],
                "Sales", financials[ Sales],
                "Profit", financials[Profit],
                "Date", financials[Date].[Date]
  )

Once again, press the run the button to run the query.

Whilst easier to read, we could do with some totals for those sales and profit figures.

Try:

EVALUATE
  SUMMARIZE(financials
            ,financials[Country]
            ,financials[Product]
            ,"Total Sales", sum(financials[ Sales])
            ,"Total Profit", sum(financials[Profit])
            )

Let’s now assume that we only want to see the results from a country. For example, Mexico.

Try:

EVALUATE
  SUMMARIZE(Filter(financials, financials[Country] = "Mexico")
            ,financials[Country]
            ,financials[Product]
            ,"Total Sales", sum(financials[ Sales])
            ,"Total Profit", sum(financials[Profit])
            )

Finally, let’s put this in order of Total Profit. To do this we need to add Order By after Summarize. Looking like this:

EVALUATE
  SUMMARIZE(Filter(financials, financials[Country] = "Mexico")
            ,financials[Country]
            ,financials[Product]
            ,"Total Sales", sum(financials[ Sales])
            ,"Total Profit", sum(financials[Profit])
            )
    Order by [Total Profit] ASC

We’ve just run through a series of queries, taking us from a simple table listing through to a filtered summary. This is the tip of the DAX query iceberg. Also, look at the query layout. In its iterations, I focused on developing the query for readability.

The DAX query view has a lot of potential, and I hope this brief introduction has helped.

If you want to know more about Power BI, training is available.

Scroll to Top