A Fine Slice Of SQL Server
Alberto Ferrari shows how you can calculate running totals in DAX:
A very common calculation in DAX is the year-to-date calculation (YTD), which aggregates values from the beginning of the year all the way to a certain date. A simple implementation uses the predefined DATESYTD function:
Sales YTD :=CALCULATE([Sales Amount],DATESYTD('Date'[Date] ))
But click through to see when this function stops being useful and what you should replace it with when it does.
Comments closedMatthew Mowrey has some tips for tuning DAX code:
Optimization has become one of my favorite topics. OK, I’ll admit it: it might be an obsession and a source of joy. My inability to optimize (queries, data models, and DAX) used to be a source of headaches and the occasional heart attack depending on what I was trying to accomplish. I make no claim that I’m an optimization expert, but I’ve spent hours wondering and researching why a data model’s calculation time could go from a couple of seconds to many minutes. This is a rare occurrence, but when it has happened, it’s happened when I’ve been on the verge of something great. Of course, if you’re taking business intelligence to its edge, you’re not adding 2+2—you’re trying to give someone an answer they (and you) thought was impossible. A recalculation that takes many minutes (especially if you’re not the end-user) is unacceptable and may cause your data model to crash—fatal exceptions anyone?
This article focuses on an optimization technique that I couldn’t find anywhere. Before I get into that technique, I’d like to touch on what I consider to be the basic tenets of optimization when working with data modeling and DAX.
Read on for the technique.
Comments closedReza Rad shows us how we can make use of the ALL function in a DAX query:
Among all the functions in DAX; the behavior of ALL function still seems mysterious for many. Many people, don’t use it at all and end up writing a very complicated calculation for a scenario that only one simple expression can do the same job. Some people, use it, but don’t exactly know how the function works, and get some unexpected results, and call it an error. In this article, I’m going to explain what ALL function is, how it can be used, and what are use cases of using such a function in DAX and Power BI. If you like to learn more about Power BI, read the Power BI book from Rookie to Rock Star.
Read on to see how the function behaves. Reza does a good job getting into the nuance of this function.
Comments closedThe rows between September 2009 and December 2009 should not be visible. The goal here is to display a blank value in these out-of-range, “future” months.
A similar issue exists for the year-over-year calculation (YOY). Even though the measure tries to show a blank value in case of missing values in current or previous year, the amounts for August 2009 and for CY 2009 might be considered wrong.
The answer is certainly not trivial but it does make for a much nicer display.
Comments closedMarco Russo takes us through some of the more common Power BI error messages around writing DAX:
The message should help the author fix the code, but sometimes the text suggests a possible action without describing the underlying issue. The goal of this article is to explain the more common DAX error messages by providing a more detailed explanation and by including links to additional material. If some terms are not clear, look at the linked articles or consider some free self-paced training such as Introducing DAX.
Click through for several examples.
Comments closedMarco Russo takes us through an issue with naming in DAX:
You deploy this model and start creating reports using the Sales Returning Customers measure. So far, so good. One day, you need to extend the data model importing a new table that you decide to name ReturningCustomers. As soon as you import the new table named ReturningCustomers, your measure Sales Returning Customers stops working. The reason is that the ReturningCustomers variable generates a name conflict with the table that has the same name, as you can see from the error message.
‘ReturningCustomers’ is a table name and cannot be used to define a variable.
Marco has some advice if you’re in a situation where you are liable to see this pop up.
Comments closedAlberto Ferrari wants to show sets of contiguous months using DAX:
Today I woke up with an interesting question, about how to show a selection of months in a nice way, detecting contiguous selection. You can easily understand the desired solution from the following figure:
I enjoyed writing a quick solution, which is worth sharing. The code is somewhat verbose, but this is mainly for educational purposes (meaning I did not want to spend time optimizing it). I will likely write a full article on it, for now, just enjoy some DAX code:
I removed the image, but to get the gist (and get you to click through to see it in its beauty), it reads “January, March-April, August-December”
Click through for Alberto’s quick-and-dirty solution and then Chris Webb’s improvement.
Comments closedThe Forecast measure in the demo model is quite an advanced piece of DAX code that would require a full article by itself. The curious reader will find more information on how to reallocate budget at different granularities in the video Budgeting with Power BI. In this article, we use the Forecast measure without detailed explanations; our goal is to explain how to compute the next measure: Remaining Forecast.
The Remaining Forecast measure must analyze the Sales table, finding the last day for which there are sales, and only then computing the forecasts.
Read the whole thing.
Comments closedMarco Russo gives us some advice on optimizing IF and SWITCH statements in DAX:
Though the DAX engine might reuse the result obtained for the same measures in the same filter context (Sales Amount and Sales LY), this is not always the case. In this scenario, variables are a good way to ensure a better optimized code execution.
However, variables should only be used within their respective scope. For example, if a variable is defined before a conditional statement, then the variable will be evaluated regardless of the condition. This has a strong performance impact in case there are disconnected slicers in the report. To elaborate on this, consider the following report where a Time Selection table is used to define a slicer that controls which columns of the matrix should be visible. The matrix contains a single measure called Sales, whose content depends on the period selected in the column.
Read on for more.
Comments closedPhilip Seamark shows us a way of splitting strings into words in DAX:
Here is a technique you might consider if you need to split text down to individual words. This could be used to help count, rank or otherwise aggregate the words in some longer text. The approach detailed here uses spaces as a delimiter and will not be tripped up if multiple spaces are used between words.
There is no SPLIT function in DAX, so this approach uses the MID function to help find words.
The PBIX file used for the blog can be downloaded here.
[Updated 14th Oct, 2018]
A slightly updated version that uses UNICHAR/UNICODE to preserve the case (“A” versus “a”) of each letter can be downloaded here. The reason for this is DAX stores a dictionary of unique values for every column. It is the first instance of any value that is added to the dictionary and assigned a new ID. Subsequent values that are considered the same “A” and “a” are considered the same are assigned the same ID. Using the UNICHAR/UNICODE version helps preserve the original case of each letter.
It’s an interesting approach and reminded me a bit of using a tally table to split strings in T-SQL.
Comments closed