Kevin Arnold

During our DFW User Group meetings we open the floor to questions. During a recent meeting an attendee asked why his measure stopped working correctly when he only changed the model to sort a column. It was a fun exercise that took many of us too long to solve.  

TL/DR

Bottom line, be careful when filtering at the column level when other columns from the table could be used in the query. The measure might need to change the context for the table instead of the column.

Getting Started

We will explore what happened using a similar development process he used to build the example. We will use the sample financial data provided with Power BI. You can add this from the initial page by clicking on “Try a sample dataset”; then “Load Sample Data”; then financials; then load.

You’ll notice this is a single table dataset at this point without any measures. Let’s add a simple ‘Sum of Sales’ measure and a simple table visual showing Country, Discount Band and [Sum of Sales]. Great it’s all working.

Next, we want to show the sales across all bands in the same visual, so we will create a new measure and add the field to the visual. Now we expect this measure to have the same value per country in our table.

The breaking change

You will notice that the bands are not sorted properly, we will add a dimension table to correct this behavior. We can do this by simply using “Enter Data”, for the band; add a relationship to ‘financials’[Discount Band] and change the visual to use the new column. We will also add a second [Sum of All Bands] measure to remove the filter on the new column. Everything still looks good.

Now, let’s add a column to properly sort ‘Band’[Band Name]. Once we make the property change for sorting our table no longer shows the correct values. Why? Let’s compare the queries produced before and after the change. You will notice that the second query now includes the ‘Band’[Band Sort] column. Our measure doesn’t remove the filter from that column.

The Fix

The measure can be corrected by changing the REMOVEFILTERS from the entire ‘Band’ table instead of the specific column. The values are now correct and the [Band Name] is properly sorted. I laid this post out in this order to show how you can be influenced to code a certain way. My preference is to create as much of the data model, in a proper Star Schema, as possible. This may have helped to produce the code correctly earlier. But it did provide for an entertaining group debugging session!