A Fine Slice Of SQL Server
Eugene Meidinger takes us through a DAX error:
Whenever you start trying to use more complicated filters in the CALCULATE or CALCULATETABLE functions in DAX, you may start to get the following error:
A function ‘MAX’ has been used in a True/False expression that is used as a table filter expression. This is not allowed.
Read on for Eugene’s explanation and what you can do about it.
Comments closedAlberto Ferrari explains what semi-additive measures are and how we can work with them in DAX:
First things first: what is a semi-additive calculation? Any calculation can be either additive, non-additive or semi-additive. An additive measure uses SUM to aggregate over any attribute. The sales amount is a perfect example of an additive measure. Indeed, the sales amount for all customers is the sum of the individual sales for each customer; at the same time, the amount over a year is the sum of the amounts for each month.
A non-additive measure does not use SUM over any dimension. Distinct count is the simplest example: the distinct count of products sold over a month is not the sum of the distinct counts of individual days. The same happens with any other dimension: a distinct count of products sold in a country is not the sum of the distinct counts of the products sold in each city in the country.
Semi-additive calculations are the hardest ones: a semi-additive measure uses SUM to aggregate over some dimensions and a different aggregation over other dimensions – a typical example being time.
Semi-additive measures are probably the trickiest of the three, as you can easily work with additive measures and you know you won’t be able to do much with non-additive measures.
Comments closedAlberto Ferrari shows how we can ignore weekends in date calculations with DAX:
How is it possible to compute the difference between the two dates, only computing working days and skipping weekends and holidays? Simple math is no longer useful here, and DAX does not offer a predefined function.
A solution to this scenario requires a date table – more details here – with a specific column, IsWorkingDay, which indicates whether that particular day is a working day or not. The following figure shows an example:
Another good use of date tables (AKA calendar tables), which are also quite useful in T-SQL queries.
Comments closedAlberto Ferrari joins Patrick to walk through how you can use DAX to format a list of values within Power BI Desktop. This takes the concatenate values quick measure to the next level.
Transmuting Adam into Alberto shows Patrick’s ultimate power.
Comments closedAlberto 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 closed