Tips on how to Write Queries for Tabular Fashions 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 distinct idea.

`EVALUATE` has solely two “Parameters”:

A desk to point out

A kind order (`ORDER BY`)

You may cross 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 need to 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:

Utilizing the DAX question view in Energy BI Desktop.
Utilizing DAX Studio.

In fact, the syntax is similar.

I desire DAX Studio over DAX question view. It presents superior options not out there in Energy BI Desktop, reminiscent of 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 adjustments in a Measure again to the mannequin immediately after I’ve modified them within the question.

I’ll focus on this later after I clarify extra about the opportunity of defining native measures. You may learn the MS documentation on modifying Measures immediately from the DAX question view.

You’ll 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 the whole Buyer desk:

Determine 1 – Easy question on the Buyer desk. The variety of returned rows might be discovered within the backside proper nook of DAX Studio, in addition to the place of the cursor within the Question (Determine by the Writer)

If I wish 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 like this:

EVALUATE { [Online Customer Count]}

The result’s one single worth:

Determine 2 – Querying a Measure with Curly brackets to outline a desk (Determine by the Writer)

Get solely the primary 10 rows

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

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

For this, TOPN() does the trick.

TOPN() accepts a sorting order. Nonetheless, it doesn’t type the info; it solely appears on the values and will get the primary or final rows in keeping with the sorting standards.

For instance, let’s get the ten prospects with the newest birthdate (Descending order):

EVALUATE TOPN(10 ,Buyer ,Buyer[BirthDate] ,DESC)

That is the consequence:

Determine 3 – Right here, the results of TOPN() is used to get the highest 10 rows by birthdate. See, that 11 rows are returned, as there are prospects with the identical birthdate (Determine by the Writer)

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

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’ll see the tie for the final worth, November 26, 1980.

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

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

And right here, the consequence:

Determine 4 – Results of the identical TOPN() question as earlier than, however with an ORDER BY to type the output of the question by the Birthday descending (Determine by the Writer)

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

Including columns

Often, I wish 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 present mixture of values in each columns. This differs from different question languages, like SQL, the place I need to explicitly outline that I wish to take away duplicates, for instance with DISTINCT.

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

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

When making an attempt these 4 capabilities, be cautious when utilizing ADDCOLUMNS(), as this operate can lead to surprising outcomes.

Learn this SQLBI article for extra particulars.

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

EVALUATE SUMMARIZECOLUMNS('Buyer'[CustomerType])

That is the consequence:

Determine 5 – Getting the Distinct values of CustomerType with SUMMARIZECOLUMNS() (Determine by the Writer)

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 present information:

Determine 6 – Getting a number of columns (Determine by the Writer)

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

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

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

That is the results of the question above:

Determine 7 – Results of the question with a number of columns and a Measure (Determine by the Writer)

You may 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 have to be a desk.

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

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

Right here, the consequence:

Determine 8 – Question and consequence to filter the Buyer-Kind to “Particular person” (Determine by the Writer)

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

You’ll find examples for this strategy on the DAX.information web page for SUMMARIZECOLUMNS().

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

You may learn my piece about amassing efficiency information to discover ways to use Efficiency Analyzer to get a question from a Visible.

It’s also possible to learn the Microsoft documentation linked beneath, which explains this.

Defining Native Measures

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

Including Measures native to the question.

The DEFINE assertion exists for this function.

For instance, we’ve got the On-line Buyer Rely Measure.
Now, I wish to add a filter to rely solely prospects of the sort “Particular person”.

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

Step one is to get the present code from the info 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”:

Determine 9 – Use the “Outline Measure” characteristic of DAX Studio to extract the DAX code for a Measure (Determine by the Writer)

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

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

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

When executing the question, the native definition of the Measure is used, as an alternative of the DAX code saved within the information mannequin:

Determine 10 – Question and outcomes with the modified DAX code for the Measure (Determine by the Writer)

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 immediately right-click the modified code and add it again to the info mannequin. Check with the hyperlink within the References part beneath for directions on how to do that.

DAX Studio doesn’t assist this characteristic.

Placing the items collectively

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

I take the question from above, change the question to record 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 cross the measure’s label, “Variety of Prospects”, as an alternative of its identify.

I need to do it this manner, as DAX replaces the measure’s identify 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:

Determine 11 – The question consequence utilizing TOPN() mixed with a Measure. Discover that the label is used as an alternative of the Measures identify (Determine by the Writer)

Conclusion

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

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

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

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

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

DAX queries have infinite use instances and needs to be a part of each Energy BI developer’s toolkit.

I hope that I used to be capable of present you one thing new and clarify why figuring out tips on how to write DAX queries is necessary for a Information mannequin developer’s every day life.

References

Microsoft’s documentation about making use of adjustments from the DAX Question view on the mannequin:

Replace mannequin with adjustments – DAX question view – Energy BI | Microsoft Be taught

Like in my earlier articles, I take advantage of the Contoso pattern dataset. You may obtain the ContosoRetailDW Dataset totally free from Microsoft right here.

The Contoso Information might be freely used below the MIT License, as described on this doc. I modified the dataset to shift the info to modern dates.

Tips on how to Write Queries for Tabular Fashions with DAX