Add a single value parameter for any column

July 4, 2021
Denis Selimovic
Data modelcomment 3Created with Sketch Beta.0 comments

This post is also available in: German


Last week I had investigated why a what-if parameter sometimes skips values and how to bypass this. In a discussion with my valued friend Bernat Agulló, we came across the topic of single values in slicers and he directed me to a tweet on how to do this with Tabular Editor.

Single values as slicer with tabular editor

In order to do this, the experimental features must first be activated in Tabular Editor. This can be done under File > Preferences:

Enable experimental features in Tabular Editor
Enable experimental features in Tabular Editor

After that, the desired column must be selected. In the Metadata settings under “Extended Properties” a new “JsonExtendedProperty” has to be created:

Adding a new JsonExtendedProperty in Tabular Editor
Adding a new JsonExtendedProperty in Tabular Editor

We specify ParameterMetadata as the name of the property and {“version”:0} as the value:

Creating the ParameterMetadata property
Creating the ParameterMetadata property

Afterwards, the data model needs to be saved in the Tabular Editor and once we return to Power BI and drag the modified column into a slicer, we have the additional option to make it a “Singel Value” slicer:

Single Value Slicer in Power BI
Single Value Slicer in Power BI

This is how the new slicer option can then be used:

Single value slicer in action
Single value slicer in action

Conclusion

I personally find the option to use a column as a single value slicer really amazing.

However, one must be aware that there are limitations. Thus, the column must be in one of the number formats (integer, floating point or currency). You should also be aware that this is an experimental feature. So it could be that if you do it wrong, the column will give you errors (so always have a backup) and that this feature will eventually stop working in future versions.