Skip to main content

This article is a continuation of last week. I recommend that you go back and read the article first if you missed it, but in short, I want to write a measure (not a calculated column) that will return the median of product sales excluding products with blanks (no sales). As I showed last week, this is relatively easy with a calculated column. Here it is again. Remember that writing the calculated columns first is a great way to visualize the problem you want to solve. It is not a good way to solve most problems (some yes, most no).

Sample Data: Adventure Works Simplified Star Schema

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

This model has a product table and a sales table, but there is no information stored anywhere that tells me the total sales for each product.

Calculate the median with a calculated column

Calculating the median is pretty easy with a calculated column. I first created a calculated column in the Adventure Works product table as shown below, just referring to the [Total Sales] measure in the new column.

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.

It turns out that when you use a column on a card in Power BI and find the middle value, the automatic visual filters the BLANKS for you, whether you like it or not. I couldn't see any way to change this behavior.

How to fix this problem with Measure and DAX Studio

The timing of this article was perfect given the recent integration of DAX Studio with Power BI Desktop via the external tools toolbar. I have given many examples of how to get value from DAX Studio in the past, and this is another similar article.

Last week I realized that I would have to do a few things to solve this problem in a Measure. I'd have to

  • Create a virtual table
  • Generate the sales column
  • Filter the blanks
  • Then do the calculation.

At first this may seem overwhelming, but if you take a step back and take each step, one step at a time, you can do it. This is how I did it.

Open DAX Studio

I turned on DAX Studio by going to the External Toolbar and launching it from there (read about how to install DAX Studio in last week's article). One of the key differences between using DAX Studio and writing DAX measures is that DAX studio MUST return a table and a measure MUST return a single scalar value. This is the exact reason why I am using DAX Studio to solve this problem. Steps 1, 2, and 3 above are all steps in the table function. I want to SEE what I am doing, THEN I will go back and to the final calculation. Trying to do it conceptually (at least when you start out) is next to impossible. Do yourself a favor and activate DAX Studio to fix the problem.

I wrote my first DAX query in the top panel (1 below) and clicked Run. The results are shown below (2). Note that the results are a table.

Every DAX query must start with the EVALUATE statement when using DAX Studio. This is not the case if you are using the New Table button in DAX or if you are embedding the DAX query within a DAX measure. If you want to learn more about DAX queries, I have a series of 4 articles that start here.

As you can see in 2 above, this query returned the entire table. Now for this exercise I am trying to get a table that contains the total sales for each product that has any sales. One principle when writing queries like this is not to return any data that you don't need. The product table is a dimension table, and that means the ProductKey column is a primary key. I can get the same end result just by using ProductKey instead of the entire Products table. I modified the query and ran it again. This is what I got. A single column that contains all the product keys.

Add a "calculated column"

In last week's article, I showed you how to create a calculated column first so you could see what you were doing and then move on to a measure. When you write DAX queries, you use a slightly different approach. There is a special function called ADDCOLUMNS that can add a new column to a table.

As you can see above, I have wrapped the original query (Line 3) inside an ADDCOLUMNS function. Line 4 specified the name of the new column and also what value would be added to the column. Do you see the similarities between writing a calculated column and the ADDCOLUMNS function?

Filter the blanks

See how much easier it is to do this when you can WATCH What's going on? I can SEE all those blanks and I know I have to get rid of them. This is where DAX as a query language is really powerful. The above code on lines 2 through 5 returns a TABLE. This table can be used as the first parameter of a FILTER function, as follows.

Notice that the FILTER function above takes a table as the first parameter (lines 3-6) and then the second parameter (line 7) filters out the zeros. Zeros, blanks, nulls, everything is the same for DAX. Also note here that line 7 refers to the column created in line 5. Now if you are familiar with the best practices, you know that it is not good practice to refer to a column unless you specify the table, that is, Table [Column]. But when you create a column using ADDCOLUMNS in a DAX query, you cannot refer to the table name (because the table has no name). That's why line 7 above looks like I'm filtering on the [Sales] measure, but it's actually the [Sales] column.

So now the table in the results window above contains the total sales for each product that has sales> 0.

Take the average result

If I now try to wrap the above table in a MEDIANX, this is what I get.

Please note that I am getting errors. Why? Do you remember what I said at the beginning? DAX Studio MUST return a table. MEDIANX returns a value, so you can't use it in DAX Studio this way.

Curly suspenders to the rescue! {}

As I mentioned last week, you can use curly braces to convert a value to a table. So I wrote this.

And DAX Studio returned a single column, single row table containing the median response I'm looking for. All that was left to do was copy the formula (lines 2-11) and paste them into a measure in Power BI. Here is the final measure

Product Sales Median = MEDIANX (FILTER (ADDCOLUMNS (ALL (Products [ProductKey]), "Sales", [Total Sales]), [Sales]> 0), [Sales])

Now this is my point. Try to write the above formula in your first 2 years of writing DAX without using DAX Studio. I'm not saying there aren't superhumans who can do this, I'm sure there are. But do yourself a favor. Break the problem into pieces and visualize each step along the journey so that you can, too.