Nesting SELECTEDVALUE in Power BI Dynamic Titles
In Power BI, you can create dynamic titles for any visuals, by creating DAX measures which track the current state of the fields displayed within the visual itself or other related slicers and visuals. These dynamic titles can be used to convey useful feedback to report consumers as they interact with your reports.
The main function used in creating the measures used to generated dynamic titles is SELECTEDVALUE
The SELECTEDVALUE function returns the value currently selected value in a column which has been filtered down to a single item. The function takes two arguments.
- ColumnName: A literal reference to an existing column; i.e., not an expression.
- AlternateResult:An optional value to be returned when a single value is not found in the specified column. When not provided, the default value is BLANK().
One useful trick when using SELECTEDVALUE to generate a dynamic title, is to use a nested SELECTEDVALUE statment as the AlternateResult argument
In the following DAX measure, we use a nested SELECTEDVALUE statement as the optional AlternateResult argument of an outer SELECTEDVALUE in the measure which generates the title of the gauge visual:
Gauge Title =
SELECTEDVALUE(
Branches[Branch],
SELECTEDVALUE(Branches[Country])
)
The measure is then assigned as the title of a gauge which displays the sales for a particular country in which our company operates. This has been achieved by placing a visual level filter on the country field, so that the values shown relate to sales made in England. The same filter has been placed on the bar chart shown below the gauge which displays the sales of each of the branches in England.
To use a measure as the title of a visual:
- In the Format tab of the Visualizations pane, expand the “Title” section and click the “fx” button.
- Choose Format by > “Field Value”; then choose your measure from the “Based on field” dropdown.
Once the title has been assigned, if no branch is selected in the bar chart, the gauge title displays the name of the country because of the nested SELECTEDVALUE statement which is acting as the AlternateResult. However, as soon as a branch is selected, the outer SELECTEDVALUE statement kicks in and returns the name of the branch.
