{"id":82626,"date":"2018-12-28T02:18:19","date_gmt":"2018-12-28T02:18:19","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=82626"},"modified":"2026-04-15T19:12:19","modified_gmt":"2026-04-15T19:12:19","slug":"creating-measures-using-dax","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/creating-measures-using-dax\/","title":{"rendered":"Creating DAX Measures in Power BI: Calculated Fields, Time Intelligence, and CALCULATE"},"content":{"rendered":"\n

Executive Summary<\/h2>\n\n\n\n

NOTE: Cannot write a precise exec summary without the article body. Once the correct docx is uploaded, write an exec summary that: (1) states what DAX measures are and when to use them over calculated columns; (2) names the main functions covered; (3) mentions the CALCULATE function explicitly since it is central to any DAX measures article. Placeholder text below:<\/strong><\/p>\n\n\n

The series so far:<\/strong><\/p>\n

    \n
  1. Creating Calculated Columns Using DAX<\/a><\/li>\n
  2. Creating Measures Using DAX<\/a><\/li>\n
  3. Using the DAX Calculate and Values Functions<\/a><\/li>\n
  4. Using the FILTER Function in DAX<\/a><\/li>\n
  5. Cracking DAX\u00a0\u2013 the EARLIER and RANKX Functions<\/a><\/li>\n
  6. Using Calendars and Dates in Power BI<\/a><\/li>\n
  7. Creating Time-Intelligence Functions in DAX<\/span><\/a><\/li>\n<\/ol>\n\n\n\n\n

    A measure<\/em> is any formula which aggregates data, whether it be counting the number of transactions in a database, summing the sales figures for a region, or working out the highest-earning salesperson for each division of a company. A measure always involves aggregating data in some way. In Power BI (and PowerPivot and SSAS Tabular) you create measures using the DAX formula language.<\/p>\n\n\n\n

    It takes a while \u2013 and a few bites of the cherry \u2013 to understand DAX properly (I\u2019ve been teaching Power BI<\/a> for some years now, and still haven\u2019t come up with a way to make it intuitive to understand). The crucial thing is to comprehend two concepts called filter context<\/em> and row context<\/em>. In this article, I\u2019ll explain what measures are, where and how to create them, and I\u2019ll explain the difference between filter and row context in DAX.<\/p>\n\n\n\n

    The Example Database for this Article<\/h2>\n\n\n\n

    This article uses the same simple database as its predecessor. This database shows sales of three toys for different cities around the world:<\/p>\n\n\n\n

    \"\"<\/figure>\n\n\n\n

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

    You can import this data into your own Power BI data model by first downloading this Excel workbook<\/a>, or by running this SQL script<\/a> in SQL Server Management Studio.<\/p>\n\n\n\n

    What are Measures?<\/h2>\n\n\n\n

    The easiest way to think of a measure is by reference to a pivot table in Excel \u2013 like this one:<\/p>\n\n\n\n

    \"\"<\/figure>\n\n\n\n

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

    The value 4 shown in the coloured box represents the total quantity of sales of the Olly Owl<\/strong> product in Cape Town<\/strong>. If you double-click on the cell, you\u2019ll see the underlying data:<\/p>\n\n\n\n

    \"\"<\/figure>\n\n\n\n

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

    The selected values sum to 4 and represent all the sales for this product (Olly Owl)<\/strong> and this city (Cape Town<\/strong>). This represents the filter context<\/em> for this cell. The pivot table doesn\u2019t sum all of the sales in this cell \u2013 just the ones which are for the product and city for this particular row and column of the pivot table.<\/p>\n\n\n\n

    The formula in the red box below \u2013 summing the quantity of goods sold for each cell in the pivot table – is a measure. <\/em>Microsoft flirted with the idea of calling it a calculated field<\/em> instead in Excel 2013, but they wisely reverted to using the term measure<\/em> for Excel 2016):<\/p>\n\n\n\n

    \"\"<\/figure>\n\n\n\n

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

    The underlying formula for this implicit measure \u2013 if you could but see it \u2013 would be =SUM(Sales[Quantity])<\/code>.<\/p>\n\n\n\n

    You\u2019ve Already Created Measures<\/h2>\n\n\n\n

    If you\u2019ve been using Power BI at all, you\u2019ll already have created measures. When you drag a field onto a table, matrix or chart, you create a hidden measure. In the diagram below, someone is about to drag the Quantity<\/code> column into the Values<\/em> section of a matrix, which will by default sum the quantity for each product and country:<\/p>\n\n\n\n

    \"\"<\/figure>\n\n\n\n

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

    Adding the Quantity<\/code> field to the Values<\/em> section of the matrix would show this \u2018measure:\u2019<\/p>\n\n\n\n

    \"\"<\/figure>\n\n\n\n

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

    In Power BI there is no way to see the DAX formula underlying this measure, but believe me, it exists, somewhere hidden away behind the scenes. PowerPivot has an advanced option allowing you to view implicit measures like this, but the Power BI elves don\u2019t want to confuse you by letting you do this.<\/p>\n\n\n\n

    Creating a Measures Table<\/h2>\n\n\n\n

    Before you create a measure, you need somewhere to put it. You can add measures to any table in your data model, but the best solution is to create a separate table to hold only measures. One way to do this is to create a new table by clicking on the Enter Data<\/em> tool in Power BI:<\/p>\n\n\n\n

    \"\"<\/figure>\n\n\n\n

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

    Leave everything as it is but overtype the name Table1<\/em> with your own name. All measures<\/em> is a good choice to ensure the table appears high up in the list alphabetically:<\/p>\n\n\n\n

    \"\"<\/figure>\n\n\n\n

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

    After clicking Load, you\u2019ll now have a nearly-empty table, in which you can create measures:<\/p>\n\n\n\n

    \"\"<\/figure>\n\n\n\n

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

    Note that you should resist the temptation to remove the useless column Column1<\/em> at this stage since Power BI would then helpfully remove the now empty table too. As soon as you\u2019ve created at least one measure in your new table, you can delete Column1<\/em>.<\/p>\n\n\n\n

    Creating a Measure<\/h2>\n\n\n\n

    To avoid being too ambitious to start with, begin by creating a basic measure to sum the quantity of goods sold. This example is completely pointless, because I\u2019ve just shown that you can get this figure by dragging the existing [<\/em><\/strong>Quantity<\/em>]<\/em><\/strong> column onto a visual, but it is a nice simple example to get you started. First, right-click on your new All measures table, and choose to add a new measure:<\/p>\n\n\n\n

    \"\"<\/figure>\n\n\n\n

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

    There are many other ways to do the same thing, but this seems the easiest. I\u2019ve covered creating new calculated columns in the previous article<\/a> in this series, and I\u2019ll show what quick measures are towards the end of this article. Here\u2019s what you\u2019ll see after choosing to create a new measure:<\/p>\n\n\n\n

    \"\"<\/figure>\n\n\n\n

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

    You can now type in any measure name and any valid formula. It\u2019s up to you whether you include blank lines and comments. Here is the formula for Total quantity sold:<\/p>\n\n\n\n

    Total quantity sold = \n\/\/ sum the quantity column\nSUM(Sales[Quantity])<\/pre>\n\n\n\n
    Your new measure will look like this in the formula bar.<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    After pressing Enter, you can now choose to display your squeaky-clean new measure in your visual:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    It would be worrying if this didn\u2019t give the same results as the implicit measure I showed earlier since it\u2019s doing the same thing:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    Finally, it would be a good idea to set default formatting for your measure, so that it will look good wherever you show it. To do this first select the measure:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    You can now choose an appropriate default format:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    Here I\u2019ve said that a comma will appear for sales of more than 999 items.<\/p>\n\n\n\n
    Aggregation Functions Allowed<\/h2>\n\n\n\n
    Here are the main aggregation functions that you can use in DAX:<\/p>\n\n\n\n
    Function<\/td>What it does for a column\u2019s values<\/td><\/tr>
    AVERAGE, AVERAGEA<\/td>Returns the average (arithmetic mean), ignoring or taking account of text entries (see hint below)<\/td><\/tr>
    COUNT<\/td>Returns the number of cells that contain numbers<\/td><\/tr>
    COUNTA<\/td>Returns the number of non-empty cells<\/td><\/tr>
    COUNTBLANK<\/td>Returns the number of blank cells<\/td><\/tr>
    COUNTROWS<\/td>Returns the number of rows in a table<\/td><\/tr>
    DISTINCTCOUNT<\/td>Returns the number of different distinct values<\/td><\/tr>
    GEOMEAN<\/td>Returns the geometric mean<\/td><\/tr>
    MAX, MAXA<\/td>Returns the largest value<\/td><\/tr>
    MEDIAN<\/td>Returns the median (the halfway point)<\/td><\/tr>
    MIN, MINA<\/td>Returns the smallest value<\/td><\/tr>
    PERCENTILE.INC, PERCENTILE.ENC<\/td>Two similar functions which return the nth<\/sup> percentile in a set of values<\/td><\/tr>
    STDEV.P<\/td>Returns the standard deviation of the entire population<\/td><\/tr>
    STDEV.S<\/td>Returns the sample standard deviation<\/td><\/tr>
    SUM<\/td>Adds the cells\u2019 values<\/td><\/tr>
    VAR.P<\/td>Returns the variance of the entire population for a column<\/td><\/tr>
    VAR.S<\/td>Returns the sample population variance for a column<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
    Aggregating Expressions \u2013 Why You Should Use Measures<\/h2>\n\n\n\n
    Suppose now that you want to sum sales, not quantities. One way to do this is to create a calculated column in the underlying table, and sum this column:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    Summing the [Sales value]<\/code> column for the above table would give the correct result, however, the method above has two main disadvantages \u2013 it will slow loading data, and it will consume more memory. I\u2019ll explain each of these disadvantages in turn.<\/p>\n\n\n\n
    When you click on a button to refresh your data, Power BI will do this in two stages although the nitty-gritty of this is hidden from you:<\/p>\n\n\n\n
    Processing<\/em> — Power BI reloads the data for each of the tables in your data model.<\/p>\n\n\n\n
    Calculation<\/em> — Power BI builds any calculated columns that you\u2019ve added to tables, among other things.<\/p>\n\n\n\n
    It\u2019s this second stage which will run more slowly since Power BI will have to reconstruct the [Sales value]<\/em> column in the [Sales]<\/em> table, even though you may never use it.<\/p>\n\n\n\n
    The second disadvantage is that the calculated column will take up more memory \u2013 probably much more memory. To see why, suppose you have data like this:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    The granularity of the columns are as follows:<\/p>\n\n\n\n
    [Price]<\/code> \u2013 3 unique values (2.50, 3.00 and 5.00)<\/p>\n\n\n\n
    [Quantity]<\/code> \u2013 3 unique values (1, 2 and 3)<\/p>\n\n\n\n
    [Sales Values]<\/code> – 7 unique values (2.50, 3.00, 5.00, 7.50, 9.00, 10.00 and 15.00)<\/p>\n\n\n\n
    Thus, the dictionaries for the calculated column will consume more memory than the original two columns\u2019 dictionaries combined. You can see much more discussion about how DAX uses column storage rather than row storage in the first article in this series<\/a>.<\/p>\n\n\n\n
    In summary, there\u2019s a trade-off between aggregating the values in a calculated column (using functions like SUM<\/code>) and aggregating the underlying expression (using a function like SUMX<\/code>). The first method uses more memory but will then run more quickly, while the second method consumes less memory but may run more slowly. There doesn\u2019t seem a clear consensus as to which method is better, so I would (as they say in the UK) \u201csuck it and see\u201d.<\/p>\n\n\n\n
    Creating Measures Using AggregateX Functions<\/h2>\n\n\n\n
    A solution to the above problem is to sum the expression [Price] * [Quantity]<\/em> from the Sales<\/em> table, but the humble SUM<\/code> function won\u2019t do this, as the IntelliSense below shows (the function is expecting a single column, not an expression):<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    Instead, you need something which will sum an expression, and for that, you just add an X onto the end of your function name:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    Here are the common function names that you can use to sum an expression across a table:<\/p>\n\n\n\n
    Function<\/td>What it does for a column\u2019s values<\/td><\/tr>
    AVERAGEX<\/td>Returns the average (arithmetic mean) of an expression<\/td><\/tr>
    COUNTX<\/td>Returns the number of cells that contain numbers<\/td><\/tr>
    COUNTAX<\/td>Returns the number of non-empty cells<\/td><\/tr>
    GEOMEANX<\/td>Returns the geometric mean<\/td><\/tr>
    MAXX<\/td>Returns the largest value<\/td><\/tr>
    MEDIANX<\/td>Returns the median (the halfway point)<\/td><\/tr>
    MINX<\/td>Returns the smallest value (named after Minnie from The Beano<\/em>)<\/td><\/tr>
    PERCENTILEX.INC, PERCENTILEX.ENC<\/td>Two similar functions which return the nth<\/sup> percentile in a set of values<\/td><\/tr>
    STDEVX.P<\/td>Returns the standard deviation of the entire population<\/td><\/tr>
    STDEVX.S<\/td>Returns the sample standard deviation<\/td><\/tr>
    SUMX<\/td>Adds the cells\u2019 values<\/td><\/tr>
    VARX.P<\/td>Returns the variance of the entire population for a column<\/td><\/tr>
    VARX.S<\/td>Returns the sample population variance for a column<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
    In this example, you could create a new measure in the [All measures]<\/em> table like this:<\/p>\n\n\n\n
    Total sales = \n    \/\/ sum the product of price and quantity\n    SUMX(\n        Sales,\n        [Price]*[Quantity]\n    )<\/pre>\n\n\n\n
    AggregateX Functions are Iterator Functions<\/h2>\n\n\n\n
    Why does Power BI have a different library of functions in order to accomplish something which is essentially the same? The answer is that, from the point of view of the DAX database, the two measures are completely different. The first function was this:<\/p>\n\n\n\n
    SUM(Sales[Quantity])<\/pre>\n\n\n\n
    Consider what this does for this cell containing the number 4:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    To calculate this, Power BI works out the filter context for this cell:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    It then sums the numbers in the Quantity<\/strong> column for the filter context. Because these numbers are all stored in one place, the calculation is very quick: 1 + 1 + 2 = 4.<\/p>\n\n\n\n
    Now consider the second measure \u2013 the one which sums an expression:<\/p>\n\n\n\n
    SUMX(Sales, [Price]*[Quantity])<\/pre>\n\n\n\n
    Here\u2019s the equivalent figure for UK sales of Olly Owl<\/strong> products:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    To calculate this figure, DAX can\u2019t just sum the value of a column. Instead, it must work its way down the rows for the filter context, multiplying the price for each product by the quantity sold:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    This will take much longer. The calculation is:<\/p>\n\n\n\n
    4.10 * 1 = 4.1<\/p>\n\n\n\n
    4.40 * 1 = 4.4<\/p>\n\n\n\n
    4.00 * 2 = 8.0<\/p>\n\n\n\n
    DAX then sums the results to get 4.10 + 4.40 + 8.00 = 16.50. To do this, it iterates over the rows, creating a row context for each. This is such an important statement that I\u2019m going to spell it out in detail. First DAX creates a row context for the first row in the filter context:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    Here\u2019s what DAX can now see:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    It multiplies the price by the quantity, stores the total and ends the row context, moving on to the next row in the filter context.<\/p>\n\n\n\n
    A function which behaves like this, which iterates down all the rows of a table, creating a row context for each row and performing some calculation before going on to the next row, is called an iterator function<\/em>.<\/p>\n\n\n\n
    Creating a Measure to Calculate a Ratio<\/h2>\n\n\n\n
    The database for this example contains two price fields. There\u2019s the price at which goods are actually sold in the Sales<\/em> table:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    However, there\u2019s also the list price for each product in the Product<\/em> table:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    A reasonable question to ask is this: for any given cell in a table or matrix, or any given data point in a chart, what is the ratio between the actual sales values of all the goods sold and what the sales value would have been if everything had sold at its list price? The answer should look like this:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    The top matrix shows the value of the sales that actually took place, the middle matrix shows what the value of these sales would have been if the list price was charged for each product in each case and the bottom matrix shows the ratio between the two values.<\/p>\n\n\n\n
    To calculate this ratio, first create a measure to sum actual sales:<\/p>\n\n\n\n
    Discounted sales values = SUMX(\n\/\/ from the sales table, sum the \n\/\/ price * the quantity for each row\n    \tSales,\n    \t[Price]*[Quantity]\n)<\/pre>\n\n\n\n
    Now create another measure which will sum the product of two figures:<\/p>\n\n\n\n
    The sales quantity from the Sales<\/strong> table; and<\/p>\n\n\n\n
    The product\u2019s list price from the Purchase<\/strong> table.<\/p>\n\n\n\n
    Here\u2019s what this formula could look like:<\/p>\n\n\n\n
    Undiscounted sales value = SUMX(\n    Sales,\n    \/\/ multiply the product's list price times\n    \/\/ the quantity sold\n    RELATED('Product'[ListPrice])*[Quantity]\n)<\/pre>\n\n\n\n
    Why is the RELATED<\/code> function needed to look up the list price for each product from a separate table? You\u2019ve already seen that measures create filter context\u2013 you don\u2019t have to specify how the underlying tables are linked together, as this is done automatically in DAX. However, the SUMX<\/code> function is an iterator function which creates a row context for each row of the specified table (in this case Sales<\/em>). Although the [Undiscounted sales value] <\/em>measure above doesn\u2019t need to cross-reference different tables, the SUMX<\/code> function within it does, since all each row knows about by default is the columns within that table:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    To find out for any row what the purchase list price was, you need to pull in a value from another table for this row context, and for that, you need to use the RELATED<\/code> function.<\/p>\n\n\n\n
    The final measure just divides one measure by another:<\/p>\n\n\n\n
    % of full value = \nDIVIDE([Discounted sales values],[Undiscounted sales value] )<\/pre>\n\n\n\n
    Or, if you prefer not to use any intermediate measures, you could do everything in one formula:<\/p>\n\n\n\n
    % of full value (version 2) = \n    DIVIDE(\n        SUMX(\n            \/\/ from the sales table, sum \n            \/\/ the price * the quantity for each row\n            Sales,\n            [Price]*[Quantity]\n        ),\n        SUMX(\n            Sales,\n            \/\/ multiply the product's list price times\n            \/\/ the quantity sold\n            RELATED('Product'[ListPrice])*[Quantity]\n        )\n    )<\/pre>\n\n\n\n
    Variables<\/h2>\n\n\n\n
    The above all-in-one formula is getting a bit complicated. Here it is again in colour:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    What it\u2019s doing for each cell of a matrix or table, or for each data point in a visual, is as follows:<\/p>\n\n\n\n
    Calculating one number (total sales for the filter context) \u2013 call this A;<\/p>\n\n\n\n
    Calculating a second number (total sales for the filter context, but using the product\u2019s list price) \u2013 call this B;<\/p>\n\n\n\n
    Dividing the first number by the second number.<\/p>\n\n\n\n
    You could make this formula easier to read by dividing this into separate stages, using variables<\/em> to hold the value of the numbers calculated along the way. For this example, you\u2019ll create the following two variables:<\/p>\n\n\n\n
    DiscountPriceSales<\/code> to hold the value of A; and<\/p>\n\n\n\n
    ListPriceSales<\/code> to hold the value of B.<\/p>\n\n\n\n
    Then divide one variable by the other to get the answer. The syntax for creating variables in a DAX measure is as follows:<\/p>\n\n\n\n
    MeasureName =\nVAR Variable1 = expression\n\u2026\nVAR VariableN = expression\nRETURN expression<\/pre>\n\n\n\n
    You can declare as few or as many variables as you like in a measure, but you must finish up by returning a value (every measure must calculate a single value for each filter context).<\/p>\n\n\n\n
    Given the above, here\u2019s what the measure could look like using variables:<\/p>\n\n\n\n
    % of full value 3 = \nVAR DiscountPriceSales = \n    SUMX(\n        \/\/ from the sales table, sum \n        \/\/ the price * the quantity for each row\n        Sales,\n        [Price]*[Quantity]\n    )\nVAR ListPriceSales = \n    SUMX(\n        Sales,\n        \/\/ multiply the product's list price times\n        \/\/ the quantity sold\n        RELATED('Product'[ListPrice])*[Quantity]\n    )\n    \nRETURN DiscountPriceSales \/ ListPriceSales<\/pre>\n\n\n\n
    It\u2019s important to realise that the two variables retain their value only while each measure is being calculated. A matrix displaying the above two measures might show this after formatting as a percent:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    DAX will generate different values of the DiscountPriceSales<\/em> and ListPriceSales<\/em> measures for each cell in the filter context. Those who are experienced in programming in other languages should note that there\u2019s no such thing as a public, static or global variable in DAX \u2013 at least, not yet.<\/p>\n\n\n\n
    Using Variables for Debugging<\/h2>\n\n\n\n
    Variables are worth using in their own right since they break complicated formulae up into smaller, more manageable chunks. However, they also have another advantage \u2013 they allow you to debug code. Suppose you think you have a problem with the formula above (the version using variables). You could comment out<\/em> some lines to experiment:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    The above example will just show the value of the first variable, but when you\u2019re happy that this is working OK, you could change things to show the value of the second variable:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    When you\u2019re happy about this too, you could reinstate the original formula.<\/p>\n\n\n\n
    DAX Studio<\/h2>\n\n\n\n
    I\u2019d like to show how I managed to produce many of the code diagrams above quickly, using a free standalone DAX editing tool called DAX Studio. You can download DAX Studio here<\/a> (one of a few possible download sites, actually), and then install it in the standard Windows way.<\/p>\n\n\n\n
    To use DAX Studio, first create a Power BI Desktop report \u2013 I\u2019ve saved the one I\u2019m working on as Variables example.pbix<\/em>:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    When you run DAX Studio, you can choose to connect to this data model:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    As the above diagram shows, you can link directly to a Power BI or SSAS Tabular data model. To link to a model in PowerPivot, you should install the Excel add-in option for DAX Studio which is out of scope for this article.<\/p>\n\n\n\n
    Here\u2019s a quick run-down of some of the things you can do in DAX Studio. Firstly, it gives you great colour-coding:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    Secondly, you can zoom in and out by holding down the Ctrl <\/strong>key and using your mouse wheel, or by using this dropdown, although Power BI has just introduced this feature, at long last):<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    Thirdly, you can drag table and column names from the model\u2019s Metadata<\/strong> on the left into the formula window:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    This example would give the following:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    And fourthly, you can easily comment out or comment back in blocks of code which is how I produced my variable measure so quickly. To do this just select the block of text that you want to comment out, or back in, then click on the appropriate tool in the DAX Studio ribbon:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    Here\u2019s what this would give:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    However, the single best thing about DAX Studio \u2013 and the reason I use it extensively \u2013 is a very simple one. When you press the ENTER key in DAX Studio, it adds a new line rather than assuming that you\u2019ve finished creating your formula and hence trying to validate it.<\/p>\n\n\n\n
    DAX Studio does have one big drawback, however \u2013 you can\u2019t use it to test a measure. After having painstakingly created a formula, it\u2019s then up to you to select the text which comprises the formula, and then copy this back into Power BI Desktop. It\u2019s worth noting that when you\u2019re writing DAX queries as shown in this article by Robert Sheldon<\/a> the exact opposite is true \u2013 you can run the DAX Queries in DAX Studio, but not within Power BI Desktop).<\/p>\n\n\n\n
    Quick Measures<\/h2>\n\n\n\n
    Future articles in this series are going to take you a long way down the murky rabbit-hole that is DAX, but it\u2019s worth mentioning that you don\u2019t actually have to write a single formula yourself \u2013 you could let Power BI do it for you, using a built-in wizard called Quick Measures<\/em>. Opinion in Wise Owl<\/a> is deeply divided on this subject (it\u2019s the Marmite<\/a> of the DAX world, although I\u2019m not sure that a UK cultural reference like this will travel well!). Some of our trainers love quick measures; I confess I don\u2019t. On the plus side, they allow you to create complicated formulae very quickly, and without any typing. My objections to them are threefold:<\/p>\n\n\n\n
    They won\u2019t help you to learn DAX, in fact, they might do exactly the opposite, as the formulae that they create can be quite off-putting;<\/p>\n\n\n\n
    Like all wizards, they generate over-complicated solutions; and<\/p>\n\n\n\n
    It\u2019s not always obvious which calculation you should choose to use.<\/p>\n\n\n\n
    Using a quick measure is thus very similar to recording a macro in VBA: you\u2019ll save yourself lots of typing, but the resulting code may be hard to understand, and it may well be written in a way which is often more complicated than a human would choose.<\/p>\n\n\n\n
    However, why not judge all this for yourself? Take a look at a couple of reasonably typical case studies of using quick measures: one to show the difference between each city\u2019s sales and the sales figure for London, and one to show the chosen values for a slicer in a card.<\/p>\n\n\n\n
    For the first one, suppose you have a column chart showing the quantity of goods sold for each city:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    You now want to show this figure relative to the figure for London, to produce this:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    To do this, first create a quick measure by right-clicking on any table and choosing to create a new quick measure (although it makes sense to put your quick measures \u2013 just like your normal measures – into a dedicated table such as the All measures<\/em> table we\u2019ve been using):<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    Choose the calculation you want to perform \u2013 in this case, it\u2019s to show the difference from a filtered value, although I don\u2019t think this is that obvious!<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    Now drag the field in that you want to aggregate, summing the quantity of goods sold:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    Finally, drag the field that you want to filter by from the fields on the right onto the Filter<\/em> section of the dialog box to get this:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    Power BI creates a new measure, automatically giving it a reasonably sensible name:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    The measure generated \u2013 like many quick measures \u2013 makes copious use of variables. It probably won\u2019t make a great deal of sense to you at the moment, as you haven\u2019t yet seen the all-important CALCULATE<\/code> function in this series of articles:<\/p>\n\n\n\n
    Quantity difference from London = \nVAR __BASELINE_VALUE = CALCULATE(SUM('Sales'[Quantity]), \n      'City'[CityName] IN { \"London\" })\nVAR __MEASURE_VALUE = SUM('Sales'[Quantity])\nRETURN\n    IF(NOT ISBLANK(__MEASURE_VALUE), __MEASURE_VALUE - __BASELINE_VALUE)<\/pre>\n\n\n\n
    For the second example, suppose you have a slicer by country:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    You want to show the countries selected in a card:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    To do this, you could create a quick measure using the same method as above and choose to concatenate the country values selected. You have to scroll right down to the bottom of the list of calculation options to find this quick measure:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    You could now drag the CountryName<\/em> field from the list of fields on the right onto your formula:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    The number of values before truncation determines how many countries you\u2019ll need to select before Power BI stops listing them, showing \u201cetc.\u201d instead. For example, if you leave this as the default value 3 \u2013 as above \u2013 and choose all of the countries, here\u2019s what you\u2019ll see:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    <\/p>\n\n\n\n
    Here\u2019s what the measure generated for this example looks like:<\/p>\n\n\n\n
    List of CountryName values = \nVAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT('Country'[CountryName])\nVAR __MAX_VALUES_TO_SHOW = 3\nRETURN\n    IF(\n        __DISTINCT_VALUES_COUNT > __MAX_VALUES_TO_SHOW,\n        CONCATENATE(\n            CONCATENATEX(\n                TOPN(\n                    __MAX_VALUES_TO_SHOW,\n                    VALUES('Country'[CountryName]),\n                    'Country'[CountryName],\n                    ASC\n                ),\n                'Country'[CountryName],\n                \", \",\n                'Country'[CountryName],\n                ASC\n            ),\n            \", etc.\"\n        ),\n        CONCATENATEX(\n            VALUES('Country'[CountryName]),\n            'Country'[CountryName],\n            \", \",\n            'Country'[CountryName],\n            ASC\n        )\n    )<\/pre>\n\n\n\n
    This is pretty hard-core DAX and probably won\u2019t make any sense at all at the moment. It\u2019s basically doing the same thing twice \u2013 once for the case where the number of countries chosen is more than 3, and once for when it\u2019s 3 or less.<\/p>\n\n\n\n
    Conclusion<\/h2>\n\n\n\n
    In this article, you\u2019ve seen that the best place to put measures that you create is in a separate table. You\u2019ve seen that measures always involve aggregation, whether this be for a single column using functions like SUM<\/code>, AVERAGE<\/code>, and COUNT<\/code> or for an expression using the same functions, but with an X suffix. These last functions are called iterator functions<\/em> and create a row context for every row in the table to which they refer. I then showed how you can create and use variables, and how you can use DAX Studio to edit your measures. Finally, finishing with two case studies of how to create quick measures to avoid typing any DAX in at all. In the next article in the series, I\u2019ll show how you can use the CALCULATE<\/code> function to change the filter context, and I\u2019ll even explain what that sentence means!<\/p>\n\n\n\n
    \n    
    FAQs: Creating Measures Using DAX<\/h2>\n\n                        
    1. What is a DAX measure in Power BI?<\/h3>\n            
    \n                
    A DAX measure is a formula that performs calculations on your data model and responds to the filter context of the report – changing its result based on which slicers, visuals, and filters are applied on the page. Unlike calculated columns (which are stored in the model and calculated during data refresh), measures are computed on the fly at query time. Measures are created in Power BI Desktop by selecting New Measure from the Modeling tab and writing a DAX formula.<\/p>\n            <\/div>\n                    
    2. What is the difference between a DAX measure and a calculated column?<\/h3>\n            
    \n                
    A calculated column is computed row-by-row during data refresh and stored in the model – it behaves like a regular column you can use in visuals, slicers, and relationships. A measure is computed at query time based on the current filter context – it aggregates data across rows and changes result based on applied filters. Use calculated columns for row-level attributes (category, lookup values, flags). Use measures for aggregations and calculations that should respond to filter context (totals, averages, YTD, ratios).<\/p>\n            <\/div>\n                    
    3. What is the CALCULATE function in DAX?<\/h3>\n            
    \n                
    CALCULATE is the most important DAX function – it evaluates a measure or expression in a modified filter context. The syntax is: CALCULATE(expression, filter1, filter2, …). The expression is evaluated with the specified filters applied on top of (or replacing) the existing filter context. Example: CALCULATE(SUM(Sales[Amount]), Products[Category] = ‘Electronics’) calculates total sales but only for the Electronics category, regardless of what other filters are applied in the visual. CALCULATE is the foundation for almost all advanced DAX including time intelligence, YTD, and ratio calculations.<\/p>\n            <\/div>\n                    
    4. What are DAX time intelligence functions in Power BI?<\/h3>\n            
    \n                
    DAX time intelligence functions calculate values relative to time periods – requiring a continuous date table in the model (typically named Date or Calendar). Key functions: TOTALYTD(Measure, DateTable[Date]) for year-to-date; SAMEPERIODLASTYEAR(DateTable[Date]) for same period last year; DATEADD(DateTable[Date], -1, YEAR) for one year prior; DATESBETWEEN for custom date ranges. These functions work within CALCULATE to modify the date filter context. A well-structured date table with no gaps from the earliest to the latest date is required for these functions to work correctly.<\/p>\n            <\/div>\n            <\/section>\n","protected":false},"excerpt":{"rendered":"
    Learn to create DAX measures in Power BI: SUM, AVERAGE, COUNT for single columns, SUMX and COUNTX iterator functions for expressions, plus variables, DAX Studio, and Quick Measures. Covers filter context vs row context.…<\/p>\n","protected":false},"author":9783,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":true,"footnotes":""},"categories":[143528,53],"tags":[],"coauthors":[6782],"class_list":["post-82626","post","type-post","status-publish","format-standard","hentry","category-bi-sql-server","category-featured"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82626","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/9783"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=82626"}],"version-history":[{"count":9,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82626\/revisions"}],"predecessor-version":[{"id":109866,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82626\/revisions\/109866"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=82626"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=82626"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=82626"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=82626"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}