New SELECTEDMEASURE function and its related functions, Explained
- mandarp0
- Jun 6, 2024
- 2 min read
Power BI has introduced a new function to reduce our redundant measures.SELECTEDMEASURE(), returns the selected measure using a slicer or filter.
There are 3 related functions to Selected Measures:
SELECTEDMEASURENAME()
ISSELECTEDMEASURE()
SELECTEDMEASUREFORMATSTRING()
We will look into all the 4 functions and explore definition, syntax, an example and remarks on when it can & cannot be called.These can be applied to Calculated columns, Calculated tables and Measure.
SELECTEDMEASURE
returns a reference to the measure that is currently in context (selected using Slicer) when the calculation item or format string is evaluated.
How to use it?
No need to create multiple measures for measures to format or apply the same calculation item.
Ex 1: expression calculates the year-to-date for whatever the measure is in context.
Ex 2: The following expression can be used to dynamically adjust the format string of a measure based upon whether a value is the hundreds, thousands, or millions.
Note:
Can only be referenced in the expression for a calculation item or format string.
This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.
SELECTEDMEASURENAME() & ISSELECTEDMEASURE()
SELECTEDMEASURENAME() returns a string value holding the name of the measure that is currently in context when the calculation item is evaluated.This function can be used for debugging purposes when authoring calculation groups.
ISSELECTEDMEASURE() returns a Boolean indicating whether the measure that is currently in context is one of those specified in the list of parameters.
Ex for SELECTEDMEASURENAME():The following calculation item expression checks if the current measure is Expense Ratio and conditionally applies calculation logic.
Since the check is based on a string comparison, it is not subject to formula fixup and will not benefit from object renaming being automatically reflected. For a similar comparison that would benefit from formula fixup, please see the ISSLECTEDMEASURE function instead.
Ex for ISSELECTEDMEASURE():
The following calculation item expression checks if the current measure is one of those specified in the list of parameters. If the measures are renamed, formula fixup will reflect the name changes in the expression.
Note:
Can only be referenced in the expression for a calculation item.
This function is not supported for use in DirectQuery mode when used in calculated
columns or row-level security (RLS) rules.
SELECTEDMEASUREFORMATSTRING()
Returns a string holding the format string of the measure that is currently in context when the calculation item is evaluated.This function can only be referenced in expressions for calculation items in calculation groups. It is designed to be used by the Format String Expression property of calculation items.Ex.
The following expression is evaluated by the Format String Expression property for a calculation item. If there is a single currency in filter context, the format string is retrieved from the DimCurrency[FormatString] column; otherwise the format string of the measure in context is used.
Note:
This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.Reference:https://learn.microsoft.com/en-us/dax/selectedmeasure-function-dax
For expert data solutions tailored to your business, contact us at Numlytics. Transform your data into actionable insights!