Category: DAX

Marco Russo and Alberto Ferrari show us how to use variables in DAX:

Variables were introduced in DAX in 2015 and so far, they have proven to be the best enhancement of the DAX language ever. When presented with the concept of variables, most newbies focus on performance improvement, thinking that you introduce variables in your code mainly to obtain better performance. Although variables can improve performance, performance is a minor advantage. There are several more important considerations that should encourage any DAX developer to make extensive use of variables. In this article we share a few considerations, along with best practices about variables and DAX.

Read on to see what makes variables so powerful.

Soheil Bakhshi explains the concept of report-level measures:

Report level measures are the measures created by the report writers within a Thin Report. Hence, the report level measures are available within the hosting Thin Report only which means the report level measures are not written back to the underlying dataset and hence they are not available to any other reports.

Read on to see how you can create one. I don’t think I’d ever heard them called that before but the name makes sense.

Marco Russo and Alberto Ferrari explain how the KEEPFILTERS function works:

KEEPFILTERS is a CALCULATE modifier used to change the way CALCULATE merges new filters with the outer filter context. Indeed, the default behavior of CALCULATE is to override existing filters. By using KEEPFILTERS you ask CALCULATE to add the new filter to the outer filter context, instead of overriding the outer filter.

Read on for the explanation and a demo.

Imke Feldmann begins a series on seasonality in Power BI:

Please note, that the monthly variation I am using in this example is taken from an existing table and not derived by statistical methods like the Excel FORECAST.ETS.SEASONALITY function. Here, we simply apply an existing distribution to expected future sales, but I will cover forecasts using those statistical methods in upcoming blogposts.

Todays starting point is a request to calculate how many sales to expect until the end of the year and then distribute the expected sales according to a list of monthly %, which are all different, because there is an expected seasonality in the sales:

Click through to see how this all works.

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.

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.