Explicit Measures vs the Filter Panel

Background

The main inspiration behind writing this article was a bug that crept into our work. The issue was that we had a year-to-date metric on a card which was erroneously showing all time, not year-to-date.

It transpired that there was no filter on the card to limit the dates to the year-to-date. Our approach was to use a base measure and then add a filter in the filter panel to restrict the dates to the year-to-date. And this filter had gone "missing" due to some subsequent development work. My experience tells me it was perhaps because of some bookmarks we had added or changed on the page at a later date.

No blame being apportioned here, this is so easy to do. I'm sure anybody reading this who has had the experience of dealing with bookmarks, especially tweaking existing ones, knows that this is prone to error and not as easy as I wish Microsoft would make it!

Introduction

There are two main ways in which we can apply context to our metrics in Power BI. So extending the example, suppose we want a card showing the sales for the year-to-date and we also want a card showing the sales for the month-to-date. The two ways we could approach this is:

1) We could use the same measure in both cards and apply the filtering of the dates using the filter panel

OR

2) We could write two specific and different measures for each scenario so that the filtering is done in the DAX measure itself.

Both approaches have their positives and negatives which we'll explore in this article.

The Case for Explicit Measures

The first advantage for this approach is transparency. At a glance of the measure logic we can see exactly what the measure does (especially if we have commented it well as mentioned in a previous article I wrote: Using the Power BI Description Box). This makes it easier and faster during debugging to understand what it does.

This approach is portable. The measure works wherever we drop it on the canvas and it will return what we expect. We don't need to remember to apply a filter / restriction in the filter pane to ensure it returns an accurate result. The cognitive load for the developer is therefore lower.

This is also easier to maintain. The calculation is self-contained and will return the correct result. If we also have bookmarks for different states on the page, we don't have to remember to apply the filters to each of the different bookmarks so the result stays accurate for all possible states. Because the logic is contained in the measure, it will be consistent across all states.

Finally, it is easier to explain. When a stakeholder questions what this value represents, we can open open the DAX and be confident in explaining what it is. All the logic is in one central place (a single source of truth). The opposite involves checking the DAX logic but then also checking the filter pane for any filters that apply to the visual or the page or the report. Slower and more complicated to explain. This more lengthy process runs the risk of eroding stakeholder trust in the numbers being returned.

The Case for Filter Pane plus Base Measures

The main advantage of this approach is reusability. We require fewer measures since, for all our metrics, we start with the same base measure and apply our context using the filter panel. This keeps the model relatively clutter-free as we need to write fewer measures.

For developers coming from a SQL background, this concept is easier to understand: using a filter in the filter pane is equivalent to using a WHERE clause, keeping this part of the logic separate from the main computation.

Finally there is more flexibility in this approach. The opportunity for analysts to self-serve is greater. They can use the base measures and then create their own metrics by applying their own filters in the filter panel.

The Pragmatist vs The Purist

As with so many things Power BI the correct answer on which to use is "it depends"!!

For enterprise level reports where reliability, auditability and interactivity matter with different views of the same data using buttons and bookmarks I would lean towards explicit measures. In this scenario I feel like maintainability is easier, the explicit nature of the measure means it is more robust to any changes or further development in the report. And additionally it is easy to understand what the measure returns.

For production reporting, I feel like explicit measures are the way to go.Trust and clarity in the report matter more to the end user than whether you have a neat and tidy model.

It doesn't take long for any Power BI report to suddenly have a lot of measures which do similar things. I would therefore caution against using an approach solely because it is more "reusable". My own opinion here is that rather than trying to limit the number of measures in the model, we should embrace the fact there is no hard limit to the number of measures which gives us the flexibility to achieve what we need much more quickly. However I would wholeheartedly recommend full commentary and documentation of measures in any case (See Using the Power BI Description Box for more details)

Fewer measures looks cleaner in the model but if the logic is hidden in filter panes, you've traded visible complexity for invisible complexity. Or put more starkly, would you rather have 50 well-named, well-documented measures or 10 cryptic measures plus 40 hidden filter pane settings scattered across 20 visuals?

However, the base measure + filter pane method has its place. For ad hoc exploration or analyst-driven (throw-away) reporting the simplicity of this approach is preferred to explicit measures. The ability to use the base measure and add your own limitations using the filter panel is exactly what you need in these scenarios.

Takeaway

Next time you get faced with the explicit measure or filter pane choice, ask yourself: "am I building a one-off analysis, or am I building a production-level, trustworthy artefact?".

The answer to that question will inform you which approach is best suited to your situation.