Press "Enter" to skip to content

Category: DAX

Counting Employees by Period with DAX

Matt Allington solves a common problem:

I’m calling this article, “How many employees by period”. Staff come and go for different reasons. In some companies, the number of staff can change over time. The principles used in this article can also be used in other instances.  There can be staff moving in and out of departments, on and off of projects, etc. The technique can also be used to work out how many staff were on leave, how many off sick, how many tickets were open in a support queue, or any other concept that has a start and end date in a transactional table.

Read on for Matt’s answer but be sure to check out the comments as there are some other good solutions in there.

Leave a Comment

Context Transition in DAX

Marco Russo and Alberto Ferrari explain the idea of context transition in DAX:

Let us state this from the very beginning: context transition is a simple concept. It is a powerful feature aiming to simplify the authoring of DAX code. That said, most new DAX developers find context transition hard to understand, and they consider it to be the major reason behind incorrect results. There are two reasons for developers to feel this way:

– A solid understanding of the difference between the row context and the filter context is an important prerequisite to understand and master the concept of context transition.

– You need to remember when and how the context transition works. Most errors involving context transition are due to the developer forgetting to take the context transition into account, rather than not knowing how it works. Once they realize that context transition is happening, the code suddenly makes sense.

Read on to understand more about the idea of context transition.

Leave a Comment

Calculations in DAX with CALCULATE()

Marco Russo and Alberto Ferrari explain one of the most important DAX functions:

CALCULATE, with its companion function CALCULATETABLE, is the only function in DAX that can change the filter context. Its use is very intuitive at first, and most DAX developers start using CALCULATE without knowing the most intricate details of its behavior. Then, sooner than later the use of CALCULATE becomes frightening because CALCULATE starts to misbehave. When this happens, it is nothing but a signal that you need to learn more theory and deepen your understanding of the behavior of CALCULATE.

In this article, we do not introduce the most complex behaviors of CALCULATE. Instead, we provide a beginner’s guide to CALCULATE, and we try to avoid making things simpler than they are. CALCULATE is definitely a complex function. Here we introduce its base behaviors, with a solid theoretical foundation.

Definitely a must-read if you work with Power BI and don’t have CALCULATE() down pat.

Comments closed

Measure Explosion and Optimization with Tabular Editor 3

Matt Allington pulls out the dynamite:

OK, so what’s the problem? The problem is that sometimes you get such a deep, nested path of measures that the underlying formula is more complex than it needs to be, and it is virtually impossible to debug. If you simply explode/expand a final measure into the full, underlying DAX code, it makes it much easier to see what is going on. Returning to the DAX example above, this is what the third measure actually looks like under the hood, removing all references to the dependent measures

Click through for an article and a video.

Comments closed

Filter Context in DAX

Marco Russo and Alberto Ferrari explain the idea of filter context:

This article is part of a series of articles about the basics of DAX. In a previous article, we introduced the first evaluation context in DAX: the row context. If you are not familiar with the row context, we strongly suggest that you start by reading that article first. Here, we build upon your knowledge of the row context to introduce the second evaluation context: the filter context.

Be mindful that the most relevant information you need to master about evaluation contexts is the difference between the row context and the filter context. You cannot appreciate any difference until you know exactly what the two contexts are. This is the reason why approaching the filter context without any existing knowledge about the row context would be only partially useful.

Read on to understand how this all differs from row context.

Comments closed

Fun with DAX in Paginated Reports

Adam Aspin looks at paginated reports:

To conclude this short set of articles on using Power BI datasets as the source of data for paginated reports, I want to outline a few classic solutions to common challenges in paginated report development with DAX.

Clearly, I cannot recount every paginated report challenge that I have ever met (or heard of) when creating reports from a Power BI dataset. However, as a report developer, it helps to be aware of some of the standard solutions to the challenges that many users encounter.

This article uses the accompanying sample data (CarsDataWarehouse.pbix) as the basis for the DAX that you will use to solve these problems.

Click through for several tips, as well as some tricks.

Comments closed

Column References in DAX

Teo Lachev makes a reference:

Suppose you use a DAX table variable, such as to group by certain columns and add an extension column as a calculation. Then, you want to count the rows in the table by filtering on one of the columns. At your first attempt, you might try using CALCULATE.

That doesn’t work and Teo explains why, as well as what you do need to use.

Comments closed

The IN Operator in DAX

Marco Russo and Alberto Ferrari are making a list and checking it twice:

The IN operator in DAX is useful in multiple scenarios to check whether an expression belongs to a list of values. It is oftentimes used along with the anonymous table constructors. IN is syntax sugar for the CONTAINSROW function. Just like CONTAINSROW, IN can be used with multiple columns at once although that syntax is not so common.

Click through to see how you can use IN in your work.

Comments closed

INT vs CONVERT in DAX

Marco Russo contrasts two functions for us:

Writing explicit type conversions is unusual in DAX, because most of the time the implicit conversion happening between different data types in an arithmetic expression provides the results you wanted. However, you might want to enforce a type conversion for different reasons: to round a number or to make sure a certain calculation is always approximated the same way. In particular, the conversion to an integer number can be obtained using different techniques – sometimes with small differences which in borderline cases might produce different results.

What we examine in this article is the difference between two techniques to convert a number to an integer: INT and CONVERT

Read on to see the differences, including where things break down for INT.

Comments closed

Guidance on SUMMARIZE() and ADDCOLUMNS() in DAX

Marco Russo has some advice for us:

Everyone using DAX is probably used to SQL query language. Because of the similarities between Tabular data modeling and relational data modeling, there is the expectation that you can perform the same operations as those allowed in SQL. However, in its current implementation DAX does not permit all the operations that you can perform in SQL. This article describes how to use ADDCOLUMNS and SUMMARIZE, which can be used in any DAX expression, including measures. For DAX queries, you should consider using SUMMARIZECOLUMNS, starting with the Introducing SUMMARIZECOLUMNS article. You can also read the All the secrets of Summarize article for more insights about inner workings of SUMMARIZE.

Read on to see how it all works.

Comments closed