Press "Enter" to skip to content

Category: DAX

DAX Error: Multiple Columns and Scalar Values

Published 2019-05-01 by Kevin Feasel

Eugene Meidinger walks us through an error message in DAX:

Sometimes, when working with DAX, you might get the following error:

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

This error occurs whenever the DAX engine was expecting a single value, or scalar, and instead received  a table of values instead. This is an easy error to make because many DAX functions, such as FILTER, SUMMARIZE and ALL, return table values. 

Eugene lays out when each scenario occurs, so check it out.

Comments closed

Aggregating Only Visible Values in DAX

Published 2019-04-24 by Kevin Feasel

Alberto Ferrari shows how you can calculate a sum based only on the visible values and ignoring the missing cases:

These measures produce the correct figures month by month. However, at the year level the number of working days might be too big whenever there are incomplete months – this always happens before the end of the year, or when the first month with sales is not January as in the following example. As you can see in the following figure, in this case the yearly value of SalesPerWorkingDays is lower than the value of any month, which does not make much sense.

Click through for the solution.

Comments closed

Using Semi-Additive Measures with DAX

Published 2019-04-02 by Kevin Feasel

Alberto 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 closed

Counting Working Days with DAX

Published 2019-03-14 by Kevin Feasel

Alberto 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 closed

Running Totals in DAX

Published 2019-03-07 by Kevin Feasel

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 closed

Performance Tuning DAX

Published 2019-03-07 by Kevin Feasel

Matthew 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 closed

Using the ALL Function in DAX

Published 2019-03-05 by Kevin Feasel

Reza 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 closed

Hiding Future Dates In DAX Measures

Published 2019-02-05 by Kevin Feasel

Marco Russo shows how we can define year-to-date measures which don’t include values for incomplete months:

The 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 closed