{"id":3244,"date":"2025-04-22T07:02:40","date_gmt":"2025-04-22T07:02:40","guid":{"rendered":"https:\/\/mailitics.com\/index.php\/2025\/04\/22\/601823-2\/"},"modified":"2025-04-22T07:02:40","modified_gmt":"2025-04-22T07:02:40","slug":"601823-2","status":"publish","type":"post","link":"https:\/\/mailitics.com\/index.php\/2025\/04\/22\/601823-2\/","title":{"rendered":"How to Write Queries for Tabular Models with DAX"},"content":{"rendered":"

How to Write Queries for Tabular Models with DAX
\n \t

\n
<\/BR>
\n
\n \t

\n
<\/BR><\/p>\n

\n

Introduction<\/mdspan><\/h2>\n

EVALUATE<\/code><\/a> is the statement to query tabular models.<\/p>\n

Unfortunately, knowing SQL or any other query language doesn\u2019t help as EVALUATE<\/code> follows a different concept.<\/p>\n

EVALUATE<\/code> has only two \u201cParameters\u201d:<\/p>\n

    \n
  1. A table to show<\/li>\n
  2. A sort order (ORDER BY<\/code><\/a>)<\/li>\n<\/ol>\n

    You can pass a third parameter (START AT<\/code><\/a>), but this one is rarely used.<\/p>\n

    However, a DAX query can have additional components. Those are defined in the DEFINE<\/code> section of the query.
    In the DEFINE<\/code> section, you can define Variables and local Measures.
    You can use the COLUMN<\/code> and TABLE<\/code> keywords in EVALUATE<\/code>, which I have never used until now.<\/p>\n

    Let\u2019s start with some simple Queries and add some additional logic step by step.<\/p>\n

    However, first, let\u2019s discuss the Tools.<\/p>\n

    Querying tools<\/h2>\n

    There are two possibilities for querying a tabular model:<\/p>\n

      \n
    1. Using the DAX query view<\/a> in Power BI Desktop.<\/li>\n
    2. Using DAX Studio<\/a>.<\/li>\n<\/ol>\n

      Of course, the syntax is the same.<\/p>\n

      I prefer DAX Studio over DAX query view. It offers advanced features not available in Power BI Desktop, such as performance statistics with Server Timing and displaying the model\u2019s metrics.<\/p>\n

      On the other hand, the DAX query view in Power BI Desktop provides the option to apply changes in a Measure back to the model directly after I have modified them in the query.<\/p>\n

      I will discuss this later when I explain more about the possibility of defining local measures. You can read the MS documentation on modifying Measures directly from the DAX query view.<\/p>\n

      You can find a link to the documentation in the References section below.<\/p>\n

      In this article, I will use DAX Studio only.<\/p>\n

      Simple queries<\/h2>\n

      The simplest query is to get all columns and all rows from a table:<\/p>\n

      EVALUATE
      \u00a0\u00a0\u00a0\u00a0 Customer<\/p>\n

      This query returns the entire Customer table:<\/p>\n

      \"\"
      Figure 1 \u2013 Simple query on the Customer table. The number of returned rows can be found in the bottom right corner of DAX Studio, as well as the position of the cursor in the Query (Figure by the Author)<\/figcaption><\/figure>\n

      If I want to query the result of a single value, for example, a Measure, I must define a table, as EVALUATE<\/code> requires a table as input.<\/p>\n

      Curly brackets do this.<\/p>\n

      Therefore, the query for a Measure looks like this:<\/p>\n

      EVALUATE<br>\u00a0\u00a0\u00a0\u00a0 { [Online Customer Count]}<\/code><\/p>\n

      The result is one single value:<\/p>\n

      \"\"
      Figure 2 \u2013 Querying a Measure with Curly brackets to define a table (Figure by the Author)<\/figcaption><\/figure>\n

      Get only the first 10 rows<\/h2>\n

      It\u2019s not unusual to have tables with thousands or even millions of rows.<\/p>\n

      So, what if I want to see the first 10 rows to glimpse the data inside the table?<\/p>\n

      For this, TOPN()<\/code><\/a> does the trick.<\/p>\n

      TOPN()<\/code> accepts a sorting order. However, it doesn\u2019t sort the data; it only looks at the values and gets the first or last rows according to the sorting criteria.<\/p>\n

      For example, let\u2019s get the ten customers with the latest birthdate (Descending order):<\/p>\n

      EVALUATE<br>\u00a0\u00a0\u00a0\u00a0TOPN(10<br>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0,Customer<br>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0,Customer[BirthDate]<br>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0,DESC)<\/code><\/p>\n

      This is the result:<\/p>\n

      \"\"
      Figure 3 \u2013 Here, the result of TOPN() is used to get the top 10 rows by birthdate. See, that 11 rows are returned, as there are customers with the same birthdate (Figure by the Author)<\/figcaption><\/figure>\n

      The DAX.guide<\/a> article on TOPN()<\/code><\/a> states the following about ties in the resulting data:<\/p>\n

      If there is a tie in OrderBy_Expression<\/code> values at the N-th row of the table, then all tied rows are returned. Then, when there are ties at the N-th row, the function might return more than n rows.<\/em><\/p>\n

      This explains why we get 11 rows from the query. When sorting the output, we will see the tie for the last value, November 26, 1980.<\/p>\n

      To have the result sorted by the Birthdate, you must add an ORDER BY<\/code>:<\/p>\n

      EVALUATE<br>\u00a0\u00a0\u00a0\u00a0TOPN(10<br>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0,Customer<br>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0,Customer[BirthDate]<br>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0,DESC)<br>\u00a0\u00a0\u00a0\u00a0ORDER BY Customer[BirthDate] DESC<\/code><\/p>\n

      And here, the result:<\/p>\n

      \"\"
      Figure 4 \u2013 Result of the same TOPN() query as before, but with an ORDER BY to sort the output of the query by the Birthday descending (Figure by the Author)<\/figcaption><\/figure>\n

      Now, the ties at the last two rows are clearly visible.<\/p>\n

      Adding columns<\/h2>\n

      Usually, I want to select only a subset of all columns in a table.<\/p>\n

      If I query multiple columns, I will only get the distinct values of the existing combination of values in both columns. This differs from other query languages, like SQL, where I must explicitly define that I want to remove duplicates, for example with DISTINCT<\/code>.<\/p>\n

      DAX has multiple functions to get a subset of columns from a table:<\/p>\n