How to Write Queries for Tabular Models with DAX

EVALUATE is the assertion to question tabular fashions.

Sadly, figuring out SQL or some other question language doesn’t assist as EVALUATE follows a special idea.

EVALUATE has solely two “Parameters”:

1. A desk to indicate
2. A kind order (ORDER BY)

You possibly can go a 3rd parameter (START AT), however this one isn’t used.

Nonetheless, a DAX question can have further parts. These are outlined within the DEFINE part of the question.
Within the DEFINE part, you’ll be able to outline Variables and native Measures.
You should use the COLUMN and TABLE key phrases in EVALUATE, which I’ve by no means used till now.

Let’s begin with some easy Queries and add some further logic step-by-step.

Nonetheless, first, let’s focus on the Instruments.

Querying instruments

There are two prospects for querying a tabular mannequin:

1. Utilizing the DAX query view in Energy BI Desktop.
2. Utilizing DAX Studio.

After all, the syntax is identical.

I favor DAX Studio over DAX question view. It presents superior options not out there in Energy BI Desktop, similar to efficiency statistics with Server Timing and displaying the mannequin’s metrics.

However, the DAX question view in Energy BI Desktop supplies the choice to use modifications in a Measure again to the mannequin instantly after I’ve modified them within the question.

I’ll focus on this later once I clarify extra about the opportunity of defining native measures. You possibly can learn the MS documentation on modifying Measures instantly from the DAX question view.

You could find a hyperlink to the documentation within the References part beneath.

On this article, I’ll use DAX Studio solely.

Easy queries

The only question is to get all columns and all rows from a desk:

EVALUATE
     Buyer

This question returns your complete Buyer desk:

If I need to question the results of a single worth, for instance, a Measure, I need to outline a desk, as EVALUATE requires a desk as enter.

Curly brackets do that.

Due to this fact, the question for a Measure appears to be like like this:

EVALUATE<br>     { [Online Customer Count]}

The result’s one single worth:

Get solely the primary 10 rows

It’s commonplace to have tables with 1000’s and even hundreds of thousands of rows.

So, what if I need to see the primary 10 rows to glimpse the information contained in the desk?

For this, TOPN() does the trick.

TOPN() accepts a sorting order. Nonetheless, it doesn’t type the information; it solely appears to be like on the values and will get the primary or final rows in response to the sorting standards.

For instance, let’s get the ten clients with the most recent birthdate (Descending order):

EVALUATE<br>    TOPN(10<br>        ,Buyer<br>        ,Buyer[BirthDate]<br>        ,DESC)

That is the consequence:

The DAX.guide article on TOPN() states the next about ties within the ensuing knowledge:

If there’s a tie in OrderBy_Expression values on the N-th row of the desk, then all tied rows are returned. Then, when there are ties on the N-th row, the operate would possibly return greater than n rows.

This explains why we get 11 rows from the question. When sorting the output, we are going to see the tie for the final worth, November 26, 1980.

To have the consequence sorted by the Birthdate, you could add an ORDER BY:

EVALUATE<br>    TOPN(10<br>        ,Buyer<br>        ,Buyer[BirthDate]<br>        ,DESC)<br>    ORDER BY Buyer[BirthDate] DESC

And right here, the consequence:

Now, the ties on the final two rows are clearly seen.

Including columns

Normally, I need to choose solely a subset of all columns in a desk.

If I question a number of columns, I’ll solely get the distinct values of the prevailing mixture of values in each columns. This differs from different question languages, like SQL, the place I need to explicitly outline that I need to take away duplicates, for instance with DISTINCT.

DAX has a number of features to get a subset of columns from a desk:

Of those 4, SUMMARIZECOLUMNS() is essentially the most helpful for common functions.

When attempting these 4 features, be cautious when utilizing ADDCOLUMNS(), as this operate may end up in surprising outcomes.

Learn this SQLBI article for extra particulars.

OK, how can we use SUMMARIZECOLUMNS() in a question:

EVALUATE<br>    SUMMARIZECOLUMNS('Buyer'[CustomerType])

That is the consequence:

As described above, we get solely the distinct values of the CustomerType column.

When querying a number of columns, the result’s the distinct mixtures of the prevailing knowledge:

Now, I can add a Measure to the Question, to get the variety of Prospects per mixture:

EVALUATE<br>    SUMMARIZECOLUMNS('Buyer'[CustomerType]<br>                        ,Buyer[Gender]<br>                        ,"Variety of Prospects", [Online Customer Count])

As you’ll be able to see, a label should be added for the Measure. This is applicable to all calculated columns added to a question.

That is the results of the question above:

You possibly can add as many columns and measures as you want.

The operate CALCULATE() is well-known for including filters to a Measure.

For queries, we are able to use the CALCULATETABLE() operate, which works like CALCULATE(); solely the primary argument should be a desk.

Right here, the identical question as earlier than, solely that the Buyer-Sort is filtered to incorporate solely “Individuals”:

EVALUATE<br>CALCULATETABLE(<br>    SUMMARIZECOLUMNS('Buyer'[CustomerType]<br>                        ,Buyer[Gender]<br>                        ,"Variety of Prospects", [Online Customer Count])<br>                ,'Buyer'[CustomerType] = "Particular person"<br>                )

Right here, the consequence:

It’s potential so as to add filters on to SUMMARIZECOLUMNS(). The queries generated by Energy BI use this method. However it’s way more sophisticated than utilizing CALCULATETABLE().

You could find examples for this method on the DAX.guide web page for SUMMARIZECOLUMNS().

Energy BI makes use of this method when constructing queries from the visualisations. You may get the queries from the Efficiency Analyzer in Energy BI Desktop.

You possibly can learn my piece about collecting performance data to learn to use Efficiency Analyzer to get a question from a Visible.

You can too learn the Microsoft documentation linked beneath, which explains this.

Defining Native Measures

From my perspective, this is likely one of the strongest options of DAX queries:

Including Measures native to the question.

The DEFINE assertion exists for this goal.

For instance, we have now the On-line Buyer Rely Measure.
 Now, I need to add a filter to rely solely clients of the kind “Particular person”.

I can modify the code within the knowledge mannequin or take a look at the logic in a DAX question.

Step one is to get the present code from the information mannequin within the present question.

For this, I need to place the cursor on the primary line of the question. Ideally, I’ll add an empty line to the question.

Now, I can use DAX Studio to extract the code of the Measure and add it to the Question by right-clicking on the Measure and clicking on “Outline Measure”:

The identical characteristic can be out there in Energy BI Desktop.

Subsequent, I can change the DAX code of the Measure by including the Filter:

DEFINE <br>---- MODEL MEASURES BEGIN ----<br>MEASURE 'All Measures'[Online Customer Count] =<br>    CALCULATE(DISTINCTCOUNT('On-line Gross sales'[CustomerKey])<br>                ,'Buyer'[CustomerType] = "Particular person"<br>                )<br>---- MODEL MEASURES END ----

When executing the question, the native definition of the Measure is used, as a substitute of the DAX code saved within the knowledge mannequin:

As soon as the DAX code works as anticipated, you’ll be able to take it and modify the Measure in Energy BI Desktop.

The DAX question view in Energy BI Desktop is advantageous as a result of you’ll be able to instantly right-click the modified code and add it again to the information mannequin. Consult with the hyperlink within the References part beneath for directions on how to do that.

DAX Studio doesn’t help this characteristic.

Placing the items collectively

OK, now let’s put the items collectively and write the next question: I need to get the highest 5 merchandise ordered by clients.

I take the question from above, change the question to listing the Product names, and add a TOPN():

DEFINE 
---- MODEL MEASURES BEGIN ----
MEASURE 'All Measures'[Online Customer Count] =
    CALCULATE(DISTINCTCOUNT('On-line Gross sales'[CustomerKey])
                ,'Buyer'[CustomerType] = "Particular person"
                )
---- MODEL MEASURES END ----

EVALUATE
    TOPN(5
        ,SUMMARIZECOLUMNS('Product'[ProductName]
                        ,"Variety of Prospects", [Online Customer Count]
                        )
        ,[Number of Customers]
        ,DESC)
    ORDER BY [Number of Customers]

Discover that I go the measure’s label, “Variety of Prospects”, as a substitute of its title.

I need to do it this fashion, as DAX replaces the measure’s title with the label. Due to this fact, DAX has no details about the Measure and solely is aware of the label.

That is the results of the question:

Conclusion

I typically use queries in DAX Studio, as it’s a lot simpler for Data Validation.

DAX Studio permits me to instantly copy the consequence into the Clipboard or write it in an Excel file with out explicitly exporting the information.

That is extraordinarily helpful when making a consequence set and sending it to my shopper for validation.

Furthermore, I can modify a Measure with out altering it in Power Bi Desktop and shortly validate the end in a desk.

I can use a Measure from the information mannequin, briefly create a modified model, and validate the outcomes side-by-side.

DAX queries have countless use instances and must be a part of each Energy BI developer’s toolkit.

I hope that I used to be in a position to present you one thing new and clarify why figuring out write DAX queries is vital for a Information mannequin developer’s day by day life.

References

Microsoft’s documentation about making use of modifications from the DAX Query view on the mannequin:

Update model with changes – DAX query view – Power BI | Microsoft Learn

Like in my earlier articles, I exploit the Contoso pattern dataset. You possibly can obtain the ContosoRetailDW Dataset free of charge from Microsoft here.

The Contoso Information might be freely used beneath the MIT License, as described in this document. I modified the dataset to shift the information to modern dates.