Dynamic titles in Power BI

Dynamic titles in Power BI

Since the April release of Power BI desktop it is possible to use dynamic titles in your visuals. This can be super handy when there is a need to explain more than just a static title. For example a static title would be “Sales over time” and a dynamic variant could be “Sales between jan 2019 and july 2019”.

To get to this option select a visual, navigate to the title menu and select “Conditional formatting” from the three dots dropdown.

In the popup that appears you can select an attribute. This can be either a column or a measure but it must be from a Text data type. If you select a column you must specify the summarization because this dynamic title can only handle a single value. To get started it is important to know what context is applied to the visual and how we can access attributes that will create our dynamic title.

Evaluation context

In DAX there is always a evaluation context. This is the context that affect a DAX expression (calculated column, measure) when it is executed. Most important is that you have an understanding about filter context, row context and context transition. You’ll start exploring these kinds of contexts whenever you build a measure that does not return the value you would expect. To explain these contexts I prefer referring to the best explanation that can be found here. In this blog I will refer mainly to the evaluation and filter context.

Getting started

First lets start with a simple dataset and some visuals.

Fig 1
Fig 2

We’ve got some data for several months and two products. The title is set to “Sales over time” and this should have an indication from the period that data is shown, lets say “Sales from 1-1-2019 until 1-6-2019”.

To do this we need to be able to grab both dates from the dataset. This can be done if we know what the evaluation context for this visual is.
The evaluation context is the environment in which a formula is evaluated and this context changes through slicers, filters, rows and columns in visuals. For this visual there is a filter context at each data point (every date) but if we look at the chart itself we could refer to the title as being the “total” value. There is no filter set by any slicer, filter, row or column value. If you look back at the data table (Fig 1) you can see that there are no rows excluded by matching the values in the visual.

Building the dynamic title

Create a new measure from the field list. To create the dynamic title the below DAX formula would do the trick.

DynamicTitle = 
VAR FirstDt =
    MIN ( Data[Date] )
VAR LastDt =
    MAX ( Data[Date] )
RETURN
    "Value from " & FirstDt & " until " & LastDt

Because dates are chronologically ordered we can grab the first date by selecting the minimal value and the max date by selecting the maximum value. I prefer to use variables because this enhances readability and has soem benefits in only calculating once.

The final line builds the dynamic title resuling in the desired outcome.

We could enhance the title in combination with the Product slicer. A slicer changes the filter context so whenever one product is selected we could use a MAX() MIN() FIRST() LAST() or any other summarization to select this product the same way as with the date but we could also concatenate the selected products by using SELECTEDVALUE()

Title = 
VAR FirstDt =
    MIN ( Data[Date] )
VAR LastDt =
    MAX ( Data[Date] )
VAR Product =
    SELECTEDVALUE (
        Data[Product];
        CONCATENATEX ( VALUES ( Data[Product] ); Data[Product]; "," )
    )
RETURN
"Value from " & FirstDt & " to " & LastDt & " for product (" & Product & ")"
Fig3 The new title with product A selected
Fig 4 With both products selected

Conclusion

Dynamic titles are a great way to give more information about what is displayed in a visual. Whenever you start with dynamic titles you should consider when this title becomes a paragraph that creates information overload. The basic idea should be that it helps understanding the visual.

Jordi

Geef een reactie