EVALUATE is the assertion to question tabular fashions.
Sadly, understanding SQL or every other question language doesn’t assist as EVALUATE follows a unique idea.
EVALUATE has solely two “Parameters”:
- A desk to indicate
- A form order (
ORDER BY)
You’ll be able to cross a 3rd parameter (START AT), however this one is never used.
Nonetheless, a DAX question can have further elements. 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:
- Utilizing the DAX query view in Energy BI Desktop.
- Utilizing DAX Studio.
After all, the syntax is identical.
I favor DAX Studio over DAX question view. It provides superior options not out there in Energy BI Desktop, corresponding to efficiency statistics with Server Timing and displaying the mannequin’s metrics.
Then again, the DAX question view in Energy BI Desktop supplies the choice to use adjustments 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 opportunity of defining native measures. You’ll be able to learn the MS documentation on modifying Measures instantly from the DAX question view.
You will discover a hyperlink to the documentation within the References part under.
On this article, I’ll use DAX Studio solely.
Easy queries
The best question is to get all columns and all rows from a desk:
EVALUATE
Buyer
This question returns the whole 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.
Due to this fact, the question for a Measure appears to be like like this:
EVALUATE
{ [Online Customer Count]}
The result’s one single worth:
Get solely the primary 10 rows
It’s common 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. 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 newest birthdate (Descending order):
EVALUATE
TOPN(10
,Buyer
,Buyer[BirthDate]
,DESC)
That is the end result:
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 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 end result sorted by the Birthdate, you need to add an ORDER BY:
EVALUATE
TOPN(10
,Buyer
,Buyer[BirthDate]
,DESC)
ORDER BY Buyer[BirthDate] DESC
And right here, the end result:
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 present 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 capabilities to get a subset of columns from a desk:
Of those 4, SUMMARIZECOLUMNS() is probably the most helpful for basic functions.
When making an attempt these 4 capabilities, be cautious when utilizing ADDCOLUMNS(), as this operate 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 end result:
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:
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:
You’ll be able to 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] = "Individual"
)
Right here, the end result:
It’s attainable so as to add filters on to SUMMARIZECOLUMNS(). The queries generated by Energy BI use this method. However it’s far more difficult than utilizing CALCULATETABLE().
You will discover 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’ll be able to learn my piece about collecting performance data to discover ways to use Efficiency Analyzer to get a question from a Visible.
You can even learn the Microsoft documentation linked under, 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 the On-line Buyer Rely 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 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 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 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] = "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’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. Check with the hyperlink within the References part under 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 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] = "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 title.
I have 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 usually use queries in DAX Studio, as it’s a lot simpler for Data Validation.
DAX Studio permits me to instantly copy the end result into the Clipboard or write it in an Excel file with out explicitly exporting the information.
That is extraordinarily helpful when making a end result 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 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 countless 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 the best way to write DAX queries is vital for a Information mannequin developer’s every day life.
References
Microsoft’s documentation about making use of adjustments 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 take advantage of the Contoso pattern dataset. You’ll be able to obtain the ContosoRetailDW Dataset totally free 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.
