A Fine Slice Of SQL Server
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.
Comments closedImke 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.
Comments closedMatt 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.
Comments closedMarco 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.
Comments closedMarco 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.
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 closedMarco 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 closedAdam 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 closedSuppose 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 closedMarco 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.