EVALUATE is the assertion to question tabular fashions.
Sadly, understanding SQL or every other question language doesn’t assist as EVALUATE follows a distinct idea.
EVALUATE has solely two “Parameters”:
- A desk to indicate
- A kind order (
ORDER BY)
You may cross a 3rd parameter (START AT), however this one isn’t used.
Nevertheless, a DAX question can have further parts. These are outlined within the DEFINE part of the question.
Within the DEFINE part, you possibly can outline Variables and native Measures.
You should utilize 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.
Nevertheless, first, let’s focus on the Instruments.
Querying instruments
There are two prospects for querying a tabular mannequin:
- Utilizing the DAX query view in Energy BI Desktop.
- Utilizing DAX Studio.
In fact, the syntax is identical.
I choose DAX Studio over DAX question view. It gives superior options not obtainable in Energy BI Desktop, comparable 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 after I clarify extra about the potential of defining native measures. You may 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 the complete Buyer desk:
If I need to question the results of a single worth, for instance, a Measure, I have to outline a desk, as EVALUATE requires a desk as enter.
Curly brackets do that.
Subsequently, the question for a Measure seems to be like this:
EVALUATE
{ [Online Customer Count]}
The result’s one single worth:
Get solely the primary 10 rows
It’s commonplace to have tables with hundreds 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. Nevertheless, it doesn’t kind the information; it solely seems to be 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
TOPN(10
,Buyer
,Buyer[BirthDate]
,DESC)
That is the consequence:
The DAX.guide 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 perform 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 will need to add an ORDER BY:
EVALUATE
TOPN(10
,Buyer
,Buyer[BirthDate]
,DESC)
ORDER BY Buyer[BirthDate] DESC
And right here, the consequence:
Now, the ties on the final two rows are clearly seen.
Including columns
Often, 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 have 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 making an attempt these 4 features, be cautious when utilizing ADDCOLUMNS(), as this perform may end up in sudden 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:
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 information:
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 possibly can 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:
You may add as many columns and measures as you want.
The perform CALCULATE() is well-known for including filters to a Measure.
For queries, we are able to use the CALCULATETABLE() perform, 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-Sort is filtered to incorporate solely “Individuals”:
EVALUATE
CALCULATETABLE(
SUMMARIZECOLUMNS('Buyer'[CustomerType]
,Buyer[Gender]
,"Variety of Prospects", [Online Customer Count])
,'Buyer'[CustomerType] = "Individual"
)
Right here, the consequence:
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 difficult than utilizing CALCULATETABLE().
You could find examples for this strategy on the DAX.guide web page for SUMMARIZECOLUMNS().
Energy BI makes use of this strategy when constructing queries from the visualisations. You will get the queries from the Efficiency Analyzer in Energy BI Desktop.
You may 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 standpoint, this is without doubt one of the strongest options of DAX queries:
Including Measures native to the question.
The DEFINE assertion exists for this goal.
For instance, now we have the On-line Buyer Depend Measure.
Now, I need to add a filter to depend solely clients of the kind “Individual”.
I can modify the code within the information mannequin or check the logic in a DAX question.
Step one is to get the present code from the information mannequin within the current question.
For this, I have 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 function can also be obtainable 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] = "Individual"
)
---- 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:
As soon as the DAX code works as anticipated, you possibly can 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 possibly can instantly right-click the modified code and add it again to the information mannequin. Confer with the hyperlink within the References part beneath for directions on how to do that.
DAX Studio doesn’t assist this function.
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 checklist 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] = "Individual"
)
---- 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 have to do it this fashion, as DAX replaces the measure’s identify with the label. Subsequently, 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 consumer for validation.
Furthermore, I can modify a Measure with out altering it in Power Bi Desktop and shortly validate the lead to a desk.
I can use a Measure from the information mannequin, quickly create a modified model, and validate the outcomes side-by-side.
DAX queries have limitless use circumstances and ought to 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 understanding methods to 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 may obtain the ContosoRetailDW Dataset free of charge from Microsoft here.
The Contoso Information may be freely used below the MIT License, as described in this document. I modified the dataset to shift the information to up to date dates.
