After EVALUATE, the next one you need to learn is the DEFINE keyword. In this tutorial, we’ll go through an overview of what the DEFINE keyword is, and how it’s used in DAX Studio. Specific examples are discussed along the way.
Using the DEFINE Keyword In DAX Studio
DEFINE is a keyword that defines entities applied to one or more EVALUATE statements in a DAX query.
Example #1: DEFINE With DAX Functions
In this example, the first line of the code retrieves all the distinct quantities in the Sales Table. The second line limits the quantities being returned. To return the result of the variable, you need to use the EVALUATE keyword.
If you try to EVALUATE the second variable, a different result appears.
You can further use the FewQuantities variable to count the number of rows in the Sales Table that pertain to quantities 1, 2, and 3.
You also need to use the ROW construct. Then apply the CALCULATE and COUNTROWS functions to count the number of rows in the Sales Table. Afterwards, place the FewQuantities variable into the filter. This gives you the total number of rows in the Sales table.
Example #2: Complex Expressions
Let’s look at another example that deals with complex expressions.
In this case, you need to use a table construction to return a column containing the quantities 1, 6, 3 and 5. For the second variable, you need the FILTER function to filter ALL the Sales Quantity IN the FewQuantities variable.
Before proceeding, run EVALUATE FilterContextFromSlicer to know if you’re getting the correct results so far.
Once that’s done, input the SUMMARIZECOLUMNS function following the code below:
This bottom section of the code mimics the behavior of Power BI.
The SUMMARIZECOLUMNS function is helpful for creating visuals and answering queries. In this case, the slicers are part of the filter context. Then, you need to add a new column, Total Rows, to display the results of the code. The last argument uses the COUNTROWS function to return the number of rows in the Sales table.
If you execute this code, you’ll get a table comprised of two columns. The first column shows the Product Color while the second shows the Total Rows.
However, the COUNTROWS (Sales) construct is not ideally used inside the SUMMARIZECOLUMNS function. Instead, the best practice is to create new measures. This will be further discussed in succeeding tutorials.
***** Related Links *****
Power Query Functions, Keywords, And Identifiers
Row Context And Filter Context In A Power BI DAX Code
How To Use The COUNTROWS DAX Function In Virtual Tables
Conclusion
This tutorial provides you with quick and specific examples on how you can use the DEFINE keyword in your DAX Studio codes. It’s also crucial in making variables work properly.
DAX Studio is a powerful tool in query building and report development. By learning the purpose and function of not just DEFINE, but all the available keywords and features, you’ll become more proficient in handling complex problems and advanced coding logic.
All the best,
Enterprise DNA Experts
