Skip to main content

I was going to title this blog "MEDIANX and X Features", but it sounded too much like a 2030s bandso I opted for MEDIANX returns blank in DAX. But the article covers more than just MEDIANX and the blank “problem”. But first some background.

Helped a customer with a strange problem this week - a measure was returning BLANK when intuitively it should have returned some value. There were a lot of moving parts to the actual problem and how I solved it, and I won't go into all of those details. However, what I will do is use my experience and what I learned this week to share some ideas for you in this article:

  • What does an X function do
  • How to build and use test tables in DAX
  • MEDIANX, vs AVERAGEX
  • The lenient way DAX evaluates the definition of BLANK

If your eyes aren't glassy by now, read on.

What does an X function do

An X function is a class of functions in DAX that are also known as "iterators" (note that there are other iterative functions as well, such as FILTER, but I'm only talking about X functions here). It's a great topic on its own, and this article won't be the definitive guide to X functions. But I'll give you a couple of ideas.

I have learned a lot about how to teach DAX to people over the past 6 years, and my teaching methods have evolved during that time. I remember speaking fondly at the Microsoft Data Insights Summit with Will Thompson on the topic "DAX 50 - DAX for the rest of us." Will told me "don't mention the word 'iterator' as it's too confusing." I disagreed with Will at the time, but his comment stuck with me. Over time, I have changed the way I teach DAX. These days, I show people how to add a calculated column to a table (everyone can do it, it's so easy). Then I explain that an X function does the exact same thing, it's just that you can't see the intermediate results materialized in front of their eyes.

Let's look at an example for clarity.

Example

I am using my standard Adventure Works simple star schema for this example.

Aw

Let's say I want to write a measure that returns the median amount spent on all customers. The problem is, I don't have the total sales for each customer in my data model. I have a customer table and a sales table, but nowhere are the total sales for each customer.

Now, I could calculate the median of the sales of all customers by first calculating the total sales of each customer in a calculated column in the customer table and then adding an image to my report to calculate the median as shown below.

Measure: Total Sales = SUM (Sales [ExtendedAmount])
I created a calculated column in the customer table: Custom Sales = [Total Sales]

Column

As you can see above, I can now see the total sales for each customer.

Then all you had to do was add an image to the report canvas (1 below), add the calculated column (2), and set the aggregation to MEDIAN (3).

Measure

As you can see in the image above, the answer is 270.27. The problem with this approach is that I had to write a calculated column to get the answer. While there is a time and place for a calculated column, you do not need to create a calculated column to get this answer. The point I am trying to make is that the column calculated above is conceptually easy for anyone to understand. It's easy to write, you can solve the problem step by step, you can see the interim results, and anyone with experience of Excel can do it with very little understanding of DAX. Understanding the MEDIANX function is much more difficult; harder until you realize that MEDIANX is doing EXACTLY the same thing as this calculated column.

Here is the MEDIANX formula.

Median measure of customer sales = MEDIANX(Customers, [Total Sales])

In the above formula

  • The X (blue) is the indicator that it is an X function. This is the clue that this is one of the functions that behaves like a calculated column.
  • The table (green) is the table that you would have written your calculated column in if you were doing it that way.
  • The measure (orange) is the formula inside the calculated column that you would have written if you had done it that way.
  • MEDIUM (red) is what happens to the numbers at the end.

Here's the lesson: An X function is exactly the same as a calculated column. The only differences are that 1) you can't WATCH what's happening and 2) doesn't permanently store row by results anywhere. But the process of doing the calculation is identical.

Now, the truth is, there is a lot going on under the hood to get this result. There is an implicit CALCULATE function hidden, there is a row context and a context transition, but the point is that it is not essential that you understand all these technical things in order to learn a little more about how to use and understand DAX. The way I teach DAX these days is to show people how to make a calculated column, then show people that the same thing can be done (in this case) using a measure and an X function. The moral here is, if you are trying to write a measure you get stuck and you know how to do it as a calculated column, then create the column first, then find out how to migrate it to a measure from there using the principle I have demonstrated.

How to create and use test tables in DAX

Now let's get back to the client problem: why did MEDIANX return a blank space? It occurred to me that the easiest way to visualize what was happening was to build a table of runtime values using the {} brace operators. I learned how useful this technique is when I watch demos and posts from two really smart DAX folks (Phil Seamark and Owen Auger).

Test measure = MEDIANX({1,2,3,6,9},[Value])

The way this measure works is that the bit in parentheses (green) generates a run-time table. The orange part [Value] allows me to access the values in the single column of the table I just created. The red and blue bits are the same as before.

The above function returned 3

So I decided to try AVERAGEX

For the record, AVERAGEX will return the average value (total divided by the count) while MEDIANX will return the average value. In case there is a tie for the mean value (that is, an even number of values), it is the mean of the 2 mean values.

Then I wrote the following test measure.

Then this

And then the penny fell for me. What if I wrote this?

These test measurements really helped me understand what was happening under the hood, and I hope it helped you too.

The DAX language is very forgiving.

DAX is a very forgiving language. In many (most) other languages, a blank or null does not evaluate to the same result as 0. In the DAX language, a blank or null will evaluate to 0 unless you explicitly tell it it doesn't, using the new == (double equals sign). In other words, 0 = BLANK () is TRUE, while 0 == BLANK () is false. I call the double equal sign "really equal," but the official name is "strictly equal to."

Test Measure = MEDIANX ({BLANK (), BLANK (), BLANK (), 6,9}, [Value])

With that in mind, the test measure that was repeated above is to evaluate the blanks as if they were numerically 0 when doing the median evaluation, and finally return the native BLANK () at the end. Of course, the difference between the average and the median is that the median returns the number from the midpoint. In this case, the midpoint is a blank space and therefore this is the result that was returned.

Now if you don't want the blanks to be treated as 0, you would have to filter them out before calculating the median.

Ignoring BLANKS () on a MEDIANX

This turned out to be a bit more complicated than I thought. First I needed a new sample to test. I created another calculated column, this time in the Products table. I knew from previous experience that not all products have sales, so this would return some blanks. Look down.

When I added this column as a card to my report and set the aggregation to MEDIUM, this is what I got.

When I wrote the equivalent test measure using MEDIANX, this is what I got

So I have to say that I was very surprised that my calculated column and measurement gave me a different result. After a little more testing and research, it turns out that when you use a column on a card in Power BI and find the median value, the visual automatically filters out the BLANKS, whether you want to or not. I couldn't see any way to change this behavior.

Part 2 next week

Solving the problem of MEDIANX as a measure is a bit more difficult than with a column. The problem is that the column doesn't actually exist when you type it as a measure, but you still have to filter the blanks before doing the calculation. You could try writing a measure like this ...

Test Measure 1 = MEDIANX (Products, if ([Total Sales] = 0, BLANK (), [Total Sales]))

but you will end up with the same result, because one of the results of the IF statement just adds the BLANK () again.

Writing a measure for this is a great topic in itself, and I'll explain how you can use DAX Studio (step by step) to solve such a problem in my next post next week.

error: Attention: Protected content.