Category: DAX

Matt Allington explains what that “X” means in functions like MEDIANX and also builds out a problem to solve:

An X function is a class of functions in DAX that are also known as “iterators” (note, there are other iterating functions too, like FILTER, but I am only referring to the X functions here). It is a big topic on its own, and this article is not going to be the definitive guide to X functions. But I will give you a couple of insights.

I have learnt a lot about how to teach people DAX over the last 6 years, and my teaching methods have evolved over that time. I remember fondly speaking at the Microsoft Data Insights Summit with Will Thompson on the topic “DAX 50 – DAX for the rest of us“. Will said to me “don’t mention the word ‘iterator’ as it is too confusing.”. I didn’t agree with Will at the time, but his comment stuck with me. Over time I have changed the day way I teach DAX. These days I show people how to add a calculated column in a table (everyone can do that – its dead easy). Then I explain that an X function does exactly the same thing, it’s just that you can’t see the interim results materialised in front of their eyes.

Click through to see what Matt means and stay tuned for the next episode of the X Functions, where Matt reveals that there are aliens by use of bi-directional relationships.

Alberto Ferrari explains why you have to mark a date table as such in Power BI:

Even though this probably does not surprise you, in fact it should. The measure – as it is written – should not work. The reason why it works is because the relationship between Sales and Date is using the Sales[Order Date] column. Sales[Order Date] has a Date data type. If we use an integer to relate the two tables instead of using a Date column, the formula stops working. We now change the relationship using the integer column Sales[OrderDateKey] instead of the Sales[Order Date].

Read the whole thing.

Ed Hansberry solves a problem in a world without window functions:

When you group data in Power Query, you are able to do a number of aggregations, like count, sum, min, max, etc. The problem is each of those aggregations only operate on that column. Let me show you what I mean. Suppose you have this data:

You need to return a table to the DAX model (or an Excel table) that only has one record per product, and you want the latest date, and the sales person associated with that date. So for product A, it is June 18, 2020 and Suresh, for B it is June 14, 2020 and Ana, and so on.

In the SQL world, this is where a combination of common table expression and ROW_NUMBER() could get the top record partitioned by product ordered by date descending. Click through for Ed’s DAX-based solutions.

Alberto Ferrari explains the LOOKUPVALUE function:

LOOKUPVALUE is one of the most widely used functions, especially for DAX developers who come from an Excel background. Indeed, the behavior of LOOKUPVALUE is very close to the behavior of the widely-adopted VLOOKUP function in Excel. Yet, there are important differences between the two; quite often, newbies use LOOKUPVALUE instead of creating a relationship between tables that ensures higher flexibility and higher performance.

This article is an introduction to LOOKUPVALUE. If you are interested in studying the internals of LOOKUPVALUE in more detail, make sure to check the advanced LOOKUPVALUE article; in that article, we describe the behavior of the function in detail along with several performance considerations.

Click through for the article.

Phil Seamark continues a discussion on single-column fusion:

You may notice the [Package – Bag] measure uses the * (multiplication) operator in line 4 between a measure and what looks like a column filter.

The [base measure] performs a simple COUNTROWS aggregation and returns an INTEGER. This side of the equation makes sense, so let’s see what is going on with the DAX on the right-hand side of the * operator? The filter statement is encompassed with parenthesis, which automatically converts the expression to a TRUE/FALSE boolean value – which is then implicitly converted to an INTEGER value of either 0 or 1. It can’t be anything else.

The result of this double conversion is that we end up with a INTEGER * INTEGER to produce the number we see in the visual.

Click through for plenty more where that came from.

Kristyna Hughes explains the concept of dynamic measures in DAX:

For example, you may have three major stakeholders looking at a report: CEO, CFO, and COO. The CEO may be more interested in future profits and looks at trends in the number of loads booked to see where we are headed. The CFO wants to see profit trends on loads that have already been completed (actualized profit). Additionally, the COO would rather see what’s currently in transit and look at only picked up loads. You could make three pages, one based on booked loads for the CEO, another based on delivered loads for the CFO, and one based on picked up loads for the COO. But what happens if your report is already going to be multiple pages and needs room to grow? You would need to triple your page count to accommodate the needs of your users or build three completely separate reports! Don’t worry, dynamic measures can solve this problem for you without clogging up your reports or workspaces with extra measures, visuals, and reports.

Click through for the demonstration on how to combine this with a slicer to change reported measures.