{"id":2355,"date":"2025-03-12T07:03:20","date_gmt":"2025-03-12T07:03:20","guid":{"rendered":"https:\/\/mailitics.com\/index.php\/2025\/03\/12\/how-to-develop-complex-dax-expressions\/"},"modified":"2025-03-12T07:03:20","modified_gmt":"2025-03-12T07:03:20","slug":"how-to-develop-complex-dax-expressions","status":"publish","type":"post","link":"https:\/\/mailitics.com\/index.php\/2025\/03\/12\/how-to-develop-complex-dax-expressions\/","title":{"rendered":"How to Develop Complex DAX\u00a0Expressions"},"content":{"rendered":"<p>    How to Develop Complex DAX\u00a0Expressions<br \/>\n \t<BR><br \/>\n<BR><\/BR><br \/>\n    <!-- no image --><br \/>\n \t<BR><br \/>\n<BR><\/BR><\/p>\n<div>\n<p class=\"wp-block-paragraph\"><em>At some point or another, any Power BI developer must write complex <a href=\"https:\/\/towardsdatascience.com\/tag\/dax\/\" title=\"Dax\">Dax<\/a> expressions to\u00a0analyze data. But nobody tells you how to do it. What\u2019s the process for doing it? What is the best way to do it, and how supportive can a development process be? These are the questions I will answer here.<\/em><\/p>\n<h2 class=\"wp-block-heading\">Introduction\u00a0<\/h2>\n<p class=\"wp-block-paragraph\">Sometimes my clients ask me how I came up with the solution for a specific measure in DAX. My answer is always that I follow a specific process to find a solution.\u00a0<\/p>\n<p class=\"wp-block-paragraph\">Sometimes, the process is not straightforward, and I must deviate or start from scratch when I\u00a0 see that I have taken the wrong direction.\u00a0<\/p>\n<p class=\"wp-block-paragraph\">But the development process is always the same:\u00a0<\/p>\n<p class=\"wp-block-paragraph\"><strong>1.<\/strong> Understand the requirements.\u00a0<\/p>\n<p class=\"wp-block-paragraph\"><strong>2.<\/strong> Define the math to calculate the result.\u00a0<\/p>\n<p class=\"wp-block-paragraph\"><strong>3.<\/strong> Understand if the measure must work in any or one specific scenario. <\/p>\n<p class=\"wp-block-paragraph\"><strong>4.<\/strong> Start with intermediary results and work my way step-by-step until I fully understand how it should work and can deliver the requested result.\u00a0<\/p>\n<p class=\"wp-block-paragraph\"><strong>5.<\/strong> Calculate the final result.\u00a0<\/p>\n<p class=\"wp-block-paragraph\">The third step is the most difficult.\u00a0<\/p>\n<p class=\"wp-block-paragraph\">Sometimes my client asks me to calculate a specific result in a particular scenario. But after I\u00a0ask again, the answer is: Yes, I will also use it in other scenarios.\u00a0<\/p>\n<p class=\"wp-block-paragraph\">For example, some time ago, a client asked me to create some measures for a specific scenario in a report. I had to do it live during a workshop with the client\u2019s team.\u00a0<\/p>\n<p class=\"wp-block-paragraph\">Days after I delivered the requested results, he asked me to create another report based on the same semantic model and logic we elaborated on during the workshop, but for a more flexible\u00a0scenario.\u00a0<\/p>\n<p class=\"wp-block-paragraph\">The first set of measures was designed to work tightly with the first scenario, so I didn\u2019t want to\u00a0change them. Therefore, I created a new set of more generic measures.\u00a0<\/p>\n<p class=\"wp-block-paragraph\">Yes, this is a worst-case scenario, but it is something that can happen.\u00a0<\/p>\n<p class=\"wp-block-paragraph\">This was just an example of how important it is to take some time to thoroughly understand the\u00a0needs and the possible future use cases for the requested measures.\u00a0<\/p>\n<h2 class=\"wp-block-heading\">Step 1: The requirements\u00a0<\/h2>\n<p class=\"wp-block-paragraph\">For this piece, I take one measure from my previous article to calculate the linear extrapolation\u00a0of my customer count.\u00a0<\/p>\n<p class=\"wp-block-paragraph\">The requirements are:<\/p>\n<ul class=\"wp-block-list\">\n<li class=\"wp-block-list-item\">Use the Customer Count Measure as the Basis Measure.\u00a0<\/li>\n<li class=\"wp-block-list-item\">The user can select the year to analyze.\u00a0<\/li>\n<li class=\"wp-block-list-item\">The user can select any other dimension in any Slicer.\u00a0<\/li>\n<li class=\"wp-block-list-item\">The User will analyze the result over time per month.\u00a0<\/li>\n<li class=\"wp-block-list-item\">The past Customer Count should be taken as the input values.\u00a0<\/li>\n<li class=\"wp-block-list-item\">The YTD growth rate must be used as the basis for the result.\u00a0<\/li>\n<li class=\"wp-block-list-item\">Based on the YTD growth rate, the Customer Count should be extrapolated to the end of\u00a0 the year.\u00a0<\/li>\n<li class=\"wp-block-list-item\">The YTD Customer Count and the Extrapolation must be shown on the same Line-Chart. <\/li>\n<\/ul>\n<p class=\"wp-block-paragraph\">The result should look like this for the year 2022:\u00a0<\/p>\n<figure class=\"wp-block-image size-full\"><img data-recalc-dims=\"1\" data-dominant-color=\"f9fafb\" data-has-transparency=\"true\" style=\"--dominant-color: #f9fafb;\" loading=\"lazy\" decoding=\"async\" width=\"454\" height=\"182\" src=\"https:\/\/i0.wp.com\/towardsdatascience.com\/wp-content\/uploads\/2025\/03\/DAX-expressions-1.png?resize=454%2C182&#038;ssl=1\" alt=\"\" class=\"wp-image-599497 has-transparency\" srcset=\"https:\/\/towardsdatascience.com\/wp-content\/uploads\/2025\/03\/DAX-expressions-1.png 454w, https:\/\/towardsdatascience.com\/wp-content\/uploads\/2025\/03\/DAX-expressions-1-300x120.png 300w\" sizes=\"auto, (max-width: 454px) 100vw, 454px\"><figcaption class=\"wp-element-caption\">Figure 1 \u2013 Requested result for the linear extrapolation of the Customer Count (Figure by the Author)\u00a0<\/figcaption><\/figure>\n<p class=\"wp-block-paragraph\">OK, let\u2019s look at how I developed this measure.<\/p>\n<p class=\"wp-block-paragraph\">But before doing so, we must understand what the filter context is.\u00a0<\/p>\n<p class=\"wp-block-paragraph\">If you are already familiar with it, you can skip this section. Or you can read it anyway to ensure\u00a0we are at the same level.\u00a0<\/p>\n<h3 class=\"wp-block-heading\">Interlude: The filter context\u00a0<\/h3>\n<p class=\"wp-block-paragraph\">The filter context is the central concept of DAX.\u00a0<\/p>\n<p class=\"wp-block-paragraph\">When writing measures in a semantic model, whether in <a href=\"https:\/\/towardsdatascience.com\/tag\/power-bi\/\" title=\"Power Bi\">Power Bi<\/a>, a fabric semantic model, or an analysis services semantic model, you must always understand the current filter context.\u00a0<\/p>\n<p class=\"wp-block-paragraph\">The filter context is:\u00a0<\/p>\n<p class=\"wp-block-paragraph\"><em>The sum of all Filters which affect the result of a DAX expression.\u00a0<\/em><\/p>\n<p class=\"wp-block-paragraph\">Look at the following picture:<\/p>\n<figure class=\"wp-block-image size-full\"><img data-recalc-dims=\"1\" data-dominant-color=\"f1f1f1\" data-has-transparency=\"true\" style=\"--dominant-color: #f1f1f1;\" loading=\"lazy\" decoding=\"async\" width=\"454\" height=\"168\" src=\"https:\/\/i0.wp.com\/towardsdatascience.com\/wp-content\/uploads\/2025\/03\/DAX-expressions-2.png?resize=454%2C168&#038;ssl=1\" alt=\"\" class=\"wp-image-599498 has-transparency\" srcset=\"https:\/\/towardsdatascience.com\/wp-content\/uploads\/2025\/03\/DAX-expressions-2.png 454w, https:\/\/towardsdatascience.com\/wp-content\/uploads\/2025\/03\/DAX-expressions-2-300x111.png 300w\" sizes=\"auto, (max-width: 454px) 100vw, 454px\"><figcaption class=\"wp-element-caption\">Figure 2 \u2013 Ask yourself: What is the Filter Context of the marked cells? (Figure by the Author) Can you explain the Filter Context of the marked cells?\u00a0<\/figcaption><\/figure>\n<p class=\"wp-block-paragraph\">Now, look at the following picture:\u00a0<\/p>\n<figure class=\"wp-block-image size-full\"><img data-recalc-dims=\"1\" data-dominant-color=\"f0eff0\" data-has-transparency=\"true\" style=\"--dominant-color: #f0eff0;\" loading=\"lazy\" decoding=\"async\" width=\"454\" height=\"168\" src=\"https:\/\/i0.wp.com\/towardsdatascience.com\/wp-content\/uploads\/2025\/03\/DAX-expressions-3.png?resize=454%2C168&#038;ssl=1\" alt=\"\" class=\"wp-image-599499 has-transparency\" srcset=\"https:\/\/towardsdatascience.com\/wp-content\/uploads\/2025\/03\/DAX-expressions-3.png 454w, https:\/\/towardsdatascience.com\/wp-content\/uploads\/2025\/03\/DAX-expressions-3-300x111.png 300w\" sizes=\"auto, (max-width: 454px) 100vw, 454px\"><figcaption class=\"wp-element-caption\">Figure 3 \u2013 All the Filters that affect the Filter Context of the marked cells (Figure by the Author)\u00a0<\/figcaption><\/figure>\n<p class=\"wp-block-paragraph\">There are six filters, that affect the filter context of the marked cells for the two measures \u201cSum\u00a0Retail Sales\u201d and \u201cAvg Retail Sales\u201d:\u00a0<\/p>\n<ul class=\"wp-block-list\">\n<li class=\"wp-block-list-item\">The Store \u201cContoso Paris Store\u201d\u00a0<\/li>\n<li class=\"wp-block-list-item\">The City \u201cParis\u201d\u00a0<\/li>\n<li class=\"wp-block-list-item\">The ClassName \u201cEconomy\u201d\u00a0<\/li>\n<li class=\"wp-block-list-item\">The Month of April 2024\u00a0<\/li>\n<li class=\"wp-block-list-item\">The Country \u201cFrance\u201d\u00a0<\/li>\n<li class=\"wp-block-list-item\">The Manufacturer \u201cProseware Inc.\u201d\u00a0<\/li>\n<\/ul>\n<p class=\"wp-block-paragraph\">The first three filters come from the visual. We can call them \u201cInternal Filters\u201d. They control how the Matrix-Visual can expand and how many details we can see.\u00a0<\/p>\n<p class=\"wp-block-paragraph\">The other filters are \u201cExternal Filters\u201d, which come from the Slicers or the Filter Pane in Power BI\u00a0 and are controlled by the user.\u00a0<\/p>\n<p class=\"wp-block-paragraph\">The Power of DAX Measures lies in the possibility of extracting the value of the Filter Context and\u00a0the capability of manipulating the Filter context.\u00a0<\/p>\n<p class=\"wp-block-paragraph\">We do this when writing DAX expressions: We manipulate the filter context.<\/p>\n<h2 class=\"wp-block-heading\">Step 2: Intermediary results\u00a0<\/h2>\n<p class=\"wp-block-paragraph\">OK, now we are good to go.\u00a0<\/p>\n<p class=\"wp-block-paragraph\">First, I do not start with the Line-Visual, but with a Table or a Matrix Visual.\u00a0<\/p>\n<p class=\"wp-block-paragraph\">This is because it\u2019s easier to see the result as a number than a line.\u00a0<\/p>\n<p class=\"wp-block-paragraph\">Even though a linear progression is visible only as a line.\u00a0<\/p>\n<p class=\"wp-block-paragraph\">However, the intermediary results are better readable in a Matrix.\u00a0<\/p>\n<p class=\"wp-block-paragraph\">If you are not familiar with working with Variables in DAX, I recommend <a href=\"https:\/\/towardsdatascience.com\/three-things-you-need-to-know-when-using-variables-in-dax-c67724862b57\/\">reading this piece<\/a>, where\u00a0 I explain the concepts for Variables:\u00a0<\/p>\n<p class=\"wp-block-paragraph\">The next step is to define the Base Measure. This is the Measure we want to use to calculate the intended Result.\u00a0<\/p>\n<p class=\"wp-block-paragraph\">As we want to calculate the YTD result, we can use a YTD Measure for the Customer Count:\u00a0<\/p>\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">Online Customer Count YTD =\nVAR YTDDates = DATESYTD('Date'[Date])\nRETURN\nCALCULATE(\nDISTINCTCOUNT('Online Sales'[CustomerKey])\n,YTDDates\n)<\/code><\/pre>\n<p class=\"wp-block-paragraph\">Now we must consider what to do with these intermediary results.\u00a0<\/p>\n<p class=\"wp-block-paragraph\">This means that we must define the arithmetic of the Measure.\u00a0<\/p>\n<p class=\"wp-block-paragraph\">For each month, I must calculate the last known Customer Count YTD.\u00a0<\/p>\n<p class=\"wp-block-paragraph\">This means, I always want to calculate 2,091 for each month. This is the last YTD Customer\u00a0 Count for the year 2022.\u00a0<\/p>\n<p class=\"wp-block-paragraph\">Then, I want to divide this result by the last month with Sales, in this case 6, for June. Then multiply it by the current month number.\u00a0<\/p>\n<p class=\"wp-block-paragraph\">Therefore, the first intermediary result is to know when the last Sale was made. We must get the\u00a0latest date in the Online Sales table for this.\u00a0<\/p>\n<p class=\"wp-block-paragraph\">According to the requirements, the User can select any year to analyze, and the result must be calculated monthly.\u00a0<\/p>\n<p class=\"wp-block-paragraph\">Therefore, the correct definition is: I must first know the month when the last sale was made for\u00a0the selected year.\u00a0<\/p>\n<p class=\"wp-block-paragraph\">The Fact table contains a date and a Relationship to the Date table, which includes the month number (Column: [Month]).<\/p>\n<p class=\"wp-block-paragraph\">So, the first variable will be something like this:\u00a0<\/p>\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">Linear extrapolation Customer Count YTD trend =\n\/\/ Get the number of months since the start of the year\nVAR LastMonthWithData = MAXX('Online Sales'\n\n,RELATED('Date'[Month])\n)\n\nRETURN\nLastMonthWithData<\/code><\/pre>\n<p class=\"wp-block-paragraph\">This is the result:\u00a0<\/p>\n<figure class=\"wp-block-image size-full\"><img data-recalc-dims=\"1\" data-dominant-color=\"f3f3f4\" data-has-transparency=\"true\" style=\"--dominant-color: #f3f3f4;\" loading=\"lazy\" decoding=\"async\" width=\"454\" height=\"215\" src=\"https:\/\/i0.wp.com\/towardsdatascience.com\/wp-content\/uploads\/2025\/03\/DAX-expressions-4.png?resize=454%2C215&#038;ssl=1\" alt=\"\" class=\"wp-image-599500 has-transparency\" srcset=\"https:\/\/towardsdatascience.com\/wp-content\/uploads\/2025\/03\/DAX-expressions-4.png 454w, https:\/\/towardsdatascience.com\/wp-content\/uploads\/2025\/03\/DAX-expressions-4-300x142.png 300w\" sizes=\"auto, (max-width: 454px) 100vw, 454px\"><figcaption class=\"wp-element-caption\">Figure 4 \u2013 Get the last month with Sales (Figure by the Author)\u00a0<\/figcaption><\/figure>\n<p class=\"wp-block-paragraph\">Hold on: We must always get the last month with sales. As it is now, we always get the same\u00a0month as the Month of the current row.\u00a0<\/p>\n<p class=\"wp-block-paragraph\">This is because each row has the Filter Context set to each month.\u00a0<\/p>\n<p class=\"wp-block-paragraph\">Therefore, we must remove the Filter for the Month, while retaining the Year. We can do this with <code>ALLEXCEPT()<\/code>:\u00a0<\/p>\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">Linear extrapolation Customer Count YTD trend =\n\/\/ Get the number of months since the start of the year\nVAR LastMonthWithData = CALCULATE(MAXX('Online Sales'\n,RELATED('Date'[Month])\n)\n,ALLEXCEPT('Date', 'Date'[Year])\n)\n\nRETURN\nLastMonthWithData<\/code><\/pre>\n<p class=\"wp-block-paragraph\">Now, the result looks much better:<\/p>\n<figure class=\"wp-block-image size-full\"><img data-recalc-dims=\"1\" data-dominant-color=\"f3f3f3\" data-has-transparency=\"true\" style=\"--dominant-color: #f3f3f3;\" loading=\"lazy\" decoding=\"async\" width=\"454\" height=\"215\" src=\"https:\/\/i0.wp.com\/towardsdatascience.com\/wp-content\/uploads\/2025\/03\/DAX-expressions-5.png?resize=454%2C215&#038;ssl=1\" alt=\"\" class=\"wp-image-599501 has-transparency\" srcset=\"https:\/\/towardsdatascience.com\/wp-content\/uploads\/2025\/03\/DAX-expressions-5.png 454w, https:\/\/towardsdatascience.com\/wp-content\/uploads\/2025\/03\/DAX-expressions-5-300x142.png 300w\" sizes=\"auto, (max-width: 454px) 100vw, 454px\"><figcaption class=\"wp-element-caption\">Figure 5 \u2013 Last month with Sales calculated for all months (Figure by the Author)\u00a0<\/figcaption><\/figure>\n<p class=\"wp-block-paragraph\">As we calculate the result for each month, we must know the month number of the current row (Month). We will reuse this as the factor for which we multiply the Average to get the linear\u00a0extrapolation.\u00a0<\/p>\n<p class=\"wp-block-paragraph\">The next intermediary result is to get the Month number:\u00a0<\/p>\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">Linear extrapolation Customer Count YTD trend =\n\/\/ Get the number of months since the start of the year\nVAR LastMonthWithData = CALCULATE(MAXX('Online Sales'\n,RELATED('Date'[Month])\n)\n,ALLEXCEPT('Date', 'Date'[Year])\n)\n\/\/ Get the last month\n\/\/ Is needed if we are looking at the data at the year, semester, or\nquarter level\nVAR MaxMonth = MAX('Date'[Month])\nRETURN\nMaxMonth<\/code><\/pre>\n<p class=\"wp-block-paragraph\">I can leave the first Variable in place and only use the MaxMonth variable after the return. The result shows the month number per month:<\/p>\n<figure class=\"wp-block-image size-full\"><img data-recalc-dims=\"1\" data-dominant-color=\"f3f3f3\" data-has-transparency=\"true\" style=\"--dominant-color: #f3f3f3;\" loading=\"lazy\" decoding=\"async\" width=\"454\" height=\"212\" src=\"https:\/\/i0.wp.com\/towardsdatascience.com\/wp-content\/uploads\/2025\/03\/DAX-expressions-6.png?resize=454%2C212&#038;ssl=1\" alt=\"\" class=\"wp-image-599502 has-transparency\" srcset=\"https:\/\/towardsdatascience.com\/wp-content\/uploads\/2025\/03\/DAX-expressions-6.png 454w, https:\/\/towardsdatascience.com\/wp-content\/uploads\/2025\/03\/DAX-expressions-6-300x140.png 300w\" sizes=\"auto, (max-width: 454px) 100vw, 454px\"><figcaption class=\"wp-element-caption\">Figure 6 \u2013 Get the current month number per row (Figure by the Author)\u00a0<\/figcaption><\/figure>\n<p class=\"wp-block-paragraph\">According to the definition formulated before, we must get the last Customer Count YTD for the latest month with Sales.\u00a0<\/p>\n<p class=\"wp-block-paragraph\">I can do this with the following Expression:\u00a0<\/p>\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">Linear extrapolation Customer Count YTD trend =\n\/\/ Get the number of months since the start of the year\nVAR LastMonthWithData = CALCULATE(MAXX('Online Sales'\n,RELATED('Date'[Month])\n)\n,ALLEXCEPT('Date', 'Date'[Year])\n)\n\/\/ Get the last month\n\/\/ Is needed if we are looking at the data at the year, semester, or\nquarter level\nVAR MaxMonth = MAX('Date'[Month])\n\/\/ Get the Customer Count YTD\nVAR LastCustomerCountYTD = CALCULATE([Online Customer Count YTD]\n,ALLEXCEPT('Date', 'Date'[Year])\n,'Date'[Month] = LastMonthWithData\n)\n\nRETURN\nLastCustomerCountYTD<\/code><\/pre>\n<p class=\"wp-block-paragraph\">As expected, the result shows 2,091 for each month:<\/p>\n<figure class=\"wp-block-image size-full\"><img data-recalc-dims=\"1\" data-dominant-color=\"f1f1f1\" data-has-transparency=\"true\" style=\"--dominant-color: #f1f1f1;\" loading=\"lazy\" decoding=\"async\" width=\"454\" height=\"212\" src=\"https:\/\/i0.wp.com\/towardsdatascience.com\/wp-content\/uploads\/2025\/03\/DAX-expressions-7.png?resize=454%2C212&#038;ssl=1\" alt=\"\" class=\"wp-image-599503 has-transparency\" srcset=\"https:\/\/towardsdatascience.com\/wp-content\/uploads\/2025\/03\/DAX-expressions-7.png 454w, https:\/\/towardsdatascience.com\/wp-content\/uploads\/2025\/03\/DAX-expressions-7-300x140.png 300w\" sizes=\"auto, (max-width: 454px) 100vw, 454px\"><figcaption class=\"wp-element-caption\">Figure 7 \u2013 Calculating the latest Customer Count YTD for each month (Figure by the Author)\u00a0<\/figcaption><\/figure>\n<p class=\"wp-block-paragraph\">You can see why I start with a table or a Matrix when developing complex Measures.\u00a0<\/p>\n<p class=\"wp-block-paragraph\">Now, imagine that one intermediary result is a date or a text.\u00a0<\/p>\n<p class=\"wp-block-paragraph\">Showing such a result in a line visual will not be practical.\u00a0<\/p>\n<p class=\"wp-block-paragraph\">We are ready to calculate the final result according to the mathematical definition above.\u00a0<\/p>\n<h2 class=\"wp-block-heading\">Step 3: The final result\u00a0<\/h2>\n<p class=\"wp-block-paragraph\">We have two ways to calculate the result:\u00a0<\/p>\n<p class=\"wp-block-paragraph\">1. Write the expression after the <code>RETURN<\/code> statement.\u00a0<\/p>\n<p class=\"wp-block-paragraph\">2. Create a new Variable \u201cResult\u201d and use this Variable after the RETURN statement. The final Expression is this:\u00a0<\/p>\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">(LastCustomerCountYTD \/ LastMonthWithData) * MaxMonth<\/code><\/pre>\n<p class=\"wp-block-paragraph\">The first Variant looks like this:\u00a0<\/p>\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">Linear extrapolation Customer Count YTD trend =\n\/\/ Get the number of months since the start of the year\nVAR LastMonthWithData = CALCULATE(MAXX('Online Sales'\n,RELATED('Date'[Month])\n\n)\n\n,ALLEXCEPT('Date', 'Date'[Year])\n\n)\n\/\/ Get the last month\n\/\/ Is needed if we are looking at the data at the year, semester, or\nquarter level\nVAR MaxMonth = MAX('Date'[Month])\n\/\/ Get the Customer Count YTD\nVAR LastCustomerCountYTD = CALCULATE([Online Customer Count YTD]\n,ALLEXCEPT('Date', 'Date'[Year])\n,'Date'[Month] = LastMonthWithData\n)\n\nRETURN\n\/\/ Calculating the extrapolation\n(LastCustomerCountYTD \/ LastMonthWithData) * MaxMonth<\/code><\/pre>\n<p class=\"wp-block-paragraph\">This is the second Variant:\u00a0<\/p>\n<pre class=\"wp-block-prismatic-blocks\"><code class=\"language-sql\">Linear extrapolation Customer Count YTD trend =\n\/\/ Get the number of months since the start of the year\nVAR LastMonthWithData = CALCULATE(MAXX('Online Sales'\n,RELATED('Date'[Month])\n)\n,ALLEXCEPT('Date', 'Date'[Year])\n)\n\/\/ Get the last month\n\/\/ Is needed if we are looking at the data at the year, semester, or\nquarter level\nVAR MaxMonth = MAX('Date'[Month])\n\/\/ Get the Customer Count YTD\nVAR LastCustomerCountYTD = CALCULATE([Online Customer Count YTD]\n,ALLEXCEPT('Date', 'Date'[Year])\n,'Date'[Month] = LastMonthWithData\n)\n\/\/ Calculating the extrapolation\nVAR Result =\n(LastCustomerCountYTD \/ LastMonthWithData) * MaxMonth\nRETURN\nResult<\/code><\/pre>\n<p class=\"wp-block-paragraph\">The result is the same.\u00a0<\/p>\n<p class=\"wp-block-paragraph\">The second variant allows us to quickly switch back to the Intermediary results if the final result\u00a0 is incorrect without needing to set the expression after the <code>RETURN<\/code> statement as a comment.\u00a0<\/p>\n<p class=\"wp-block-paragraph\">It simply makes life easier.\u00a0<\/p>\n<p class=\"wp-block-paragraph\">But it\u2019s up to you which variant you like more.\u00a0<\/p>\n<p class=\"wp-block-paragraph\">The result is this:<\/p>\n<figure class=\"wp-block-image size-full\"><img data-recalc-dims=\"1\" data-dominant-color=\"f1f1f2\" data-has-transparency=\"true\" style=\"--dominant-color: #f1f1f2;\" loading=\"lazy\" decoding=\"async\" width=\"454\" height=\"212\" src=\"https:\/\/i0.wp.com\/towardsdatascience.com\/wp-content\/uploads\/2025\/03\/DAX-expressions-8.png?resize=454%2C212&#038;ssl=1\" alt=\"\" class=\"wp-image-599504 has-transparency\" srcset=\"https:\/\/towardsdatascience.com\/wp-content\/uploads\/2025\/03\/DAX-expressions-8.png 454w, https:\/\/towardsdatascience.com\/wp-content\/uploads\/2025\/03\/DAX-expressions-8-300x140.png 300w\" sizes=\"auto, (max-width: 454px) 100vw, 454px\"><figcaption class=\"wp-element-caption\">Figure 8 \u2013 Final result in a table (Figure by the Author)\u00a0<\/figcaption><\/figure>\n<p class=\"wp-block-paragraph\">When converting this table to a Line Visual, we get the same result as in the first figure. The last step will be to set the line as a Dashed line, to get the needed visualization. <\/p>\n<figure class=\"wp-block-image size-full\"><img data-recalc-dims=\"1\" data-dominant-color=\"f8f8f9\" data-has-transparency=\"true\" style=\"--dominant-color: #f8f8f9;\" loading=\"lazy\" decoding=\"async\" width=\"454\" height=\"152\" src=\"https:\/\/i0.wp.com\/towardsdatascience.com\/wp-content\/uploads\/2025\/03\/DAX-expressions-9.png?resize=454%2C152&#038;ssl=1\" alt=\"\" class=\"wp-image-599505 has-transparency\" srcset=\"https:\/\/towardsdatascience.com\/wp-content\/uploads\/2025\/03\/DAX-expressions-9.png 454w, https:\/\/towardsdatascience.com\/wp-content\/uploads\/2025\/03\/DAX-expressions-9-300x100.png 300w\" sizes=\"auto, (max-width: 454px) 100vw, 454px\"><figcaption class=\"wp-element-caption\">Figure 9 \u2013 Set the line for the extrapolation as a dashed line (Figure by the Author)\u00a0<\/figcaption><\/figure>\n<h3 class=\"wp-block-heading\">Complex calculated columns\u00a0<\/h3>\n<p class=\"wp-block-paragraph\">The process is the same when writing complex DAX expressions for calculated columns. The difference is that we can see the result in the Table View of Power BI Desktop.\u00a0<\/p>\n<p class=\"wp-block-paragraph\">Be aware that when calculated columns are calculated, the results are physically stored in the table when you press Enter.\u00a0<\/p>\n<p class=\"wp-block-paragraph\">The results of Measures are not stored in the Model. They are calculated on the fly in the\u00a0Visualizations.\u00a0<\/p>\n<p class=\"wp-block-paragraph\">Another difference is that we can leverage Context Transition to get our result when we need it to depend on other rows in the table.\u00a0<\/p>\n<p class=\"wp-block-paragraph\">Read this piece to learn more about this fascinating topic:\u00a0<\/p>\n<figure class=\"wp-block-embed is-type-wp-embed is-provider-towards-data-science wp-block-embed-towards-data-science\">\n<div class=\"wp-block-embed__wrapper\">\n<blockquote class=\"wp-embedded-content\" data-secret=\"2i6Kzm291E\"><p><a href=\"https:\/\/towardsdatascience.com\/whats-fancy-about-context-transition-in-dax-efb5d5bc4c01\/\">What\u2019s fancy about context transition in DAX<\/a><\/p><\/blockquote>\n<p><iframe loading=\"lazy\" class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" title=\"\u201cWhat\u2019s fancy about context transition in DAX\u201d \u2014 Towards Data Science\" src=\"https:\/\/towardsdatascience.com\/whats-fancy-about-context-transition-in-dax-efb5d5bc4c01\/embed\/#?secret=0hiLYcJ7VU#?secret=2i6Kzm291E\" data-secret=\"2i6Kzm291E\" width=\"500\" height=\"282\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe>\n<\/div>\n<\/figure>\n<h2 class=\"wp-block-heading\">Conclusion\u00a0<\/h2>\n<p class=\"wp-block-paragraph\">The development process for complex expressions always follows the same steps:\u00a0<\/p>\n<p class=\"wp-block-paragraph\"><strong>1.<\/strong> Understand the requirements \u2013 Ask if something is unclear.\u00a0<\/p>\n<p class=\"wp-block-paragraph\"><strong>2.<\/strong> Define the math for the results.\u00a0<\/p>\n<p class=\"wp-block-paragraph\"><strong>3.<\/strong> Start with intermediary results and understand the results.\u00a0<\/p>\n<p class=\"wp-block-paragraph\"><strong>4.<\/strong> Build on the intermediary results one by one \u2013 Do not try to write all in one step. <\/p>\n<p class=\"wp-block-paragraph\"><strong>5.<\/strong> Decide where to write the expression for the final result.\u00a0<\/p>\n<p class=\"wp-block-paragraph\">Following such a process can save you the day, as you don\u2019t need to write everything in one step.\u00a0<\/p>\n<p class=\"wp-block-paragraph\">Moreover, getting these intermediary results allows you to understand what\u2019s happening and\u00a0explore the Filter Context.\u00a0<\/p>\n<p class=\"wp-block-paragraph\">This will help you learn DAX more efficiently and build even more complex stuff.\u00a0<\/p>\n<p class=\"wp-block-paragraph\">But, be aware: Even though a certain level of complexity is needed, a good developer will keep it as simple as possible, while maintaining the least amount of complexity.\u00a0<\/p>\n<h2 class=\"wp-block-heading\">References\u00a0<\/h2>\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/towardsdatascience.com\/calculating-a-linear-extrapolation-or-trend-in-dax-72a5705d949c\/\">Here<\/a> is the article mentioned at the beginning of this piece, to calculate the linear interpolation.<\/p>\n<p class=\"wp-block-paragraph\">Like in my previous articles, I use the Contoso sample dataset. You can download the\u00a0 ContosoRetailDW Dataset for free from Microsoft <a href=\"https:\/\/www.microsoft.com\/en-us\/download\/details.aspx?id=18279\">here<\/a>.<\/p>\n<p class=\"wp-block-paragraph\">The Contoso Data can be freely used under the MIT License, as described <a href=\"https:\/\/github.com\/microsoft\/Power-BI-Embedded-Contoso-Sales-Demo\">here<\/a>. I changed the dataset to shift the data to contemporary dates.<\/p>\n<p>The post <a href=\"https:\/\/towardsdatascience.com\/how-to-develop-complex-dax-expressions\/\">How to Develop Complex DAX\u00a0Expressions<\/a> appeared first on <a href=\"https:\/\/towardsdatascience.com\/\">Towards Data Science<\/a>.<\/p>\n<\/div>\n<p> \t<BR><br \/>\n <BR><\/BR><br \/>\n    Salvatore Cagliari<br \/>\n \t<BR><br \/>\n<BR><\/BR><br \/>\n<a href=\"https:\/\/towardsdatascience.com\/how-to-develop-complex-dax-expressions\/\">Go to original source<\/a><br \/>\n \t<BR><br \/>\n <BR><\/BR><\/p>\n","protected":false},"excerpt":{"rendered":"<p>How to Develop Complex DAX\u00a0Expressions At some point or another, any Power BI developer must write complex Dax expressions to\u00a0analyze data. But nobody tells you how to do it. What\u2019s the process for doing it? What is the best way to do it, and how supportive can a development process be? These are the questions [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[62,1995,1853,83,1511,1360,160],"tags":[267,7,1996],"class_list":["post-2355","post","type-post","status-publish","format-standard","hentry","category-aimldsaimlds","category-business-analytics","category-data-analyitcs","category-data-science","category-dax","category-power-bi","category-programming","tag-but","tag-how","tag-scenario"],"_links":{"self":[{"href":"https:\/\/mailitics.com\/index.php\/wp-json\/wp\/v2\/posts\/2355"}],"collection":[{"href":"https:\/\/mailitics.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mailitics.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mailitics.com\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/mailitics.com\/index.php\/wp-json\/wp\/v2\/comments?post=2355"}],"version-history":[{"count":0,"href":"https:\/\/mailitics.com\/index.php\/wp-json\/wp\/v2\/posts\/2355\/revisions"}],"wp:attachment":[{"href":"https:\/\/mailitics.com\/index.php\/wp-json\/wp\/v2\/media?parent=2355"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mailitics.com\/index.php\/wp-json\/wp\/v2\/categories?post=2355"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mailitics.com\/index.php\/wp-json\/wp\/v2\/tags?post=2355"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}