Using SELECTEDVALUE with Fields Parameters in Power BI

If you try to use SELECTEDVALUE on the visible column of the table generated by the Fields Parameters feature in Power BI, you get the following error:

Calculation error in measure ‘Sales'[Selection]: Column [Parameter] is part of composite key, but not all columns of the composite key are included in the expression or its dependent expression.

This error is generated because the Parameter column in the Parameter table uses a column property called Group By Columns (visible through Tabular Editor) that is barely documented in the Microsoft documentation. We explained such a feature Grouping Columns section of the Tabular Presentation Layer in the Mastering Tabular video course. Group By Columns is not well documented and not suggested to be used in regular models, mainly because it is not supported in MDX queries (so it is not supported by Excel). The feature was there because of the compatibility required to connect with particular data sources. However, now that a popular feature such as Fields Parameters uses Group By Columns, it is time to talk more about it.

The basic idea is that the Group By Columns feature defines a set of columns that can be used as “multi-part-key” for a column (Sort By Column only uses a single column for the sort order and does not define a “key” for a column). When this feature is used, retrieving the column without the column(s) that is/are part of the key is no longer possible.

The table created by Fields Parameters uses a single column as a key, but this makes it impossible to use HASONEVALUE ( Parameter[Parameter] ) in your code. The consequence is that all the functions that internally use HASONEVALUE over that column cannot be used, and SELECTEDVALUE is one of them. You should use COUNTROWS ( … ) = 1 instead of HASONEVALUE and replace SELECTEDVALUE with a longer DAX construct.

Instead of writing:

SELECTEDVALUE ( Parameter[Parameter] )

You can write:

VAR __SelectedValue = 
    SELECTCOLUMNS (
        SUMMARIZE ( Parameter, Parameter[Parameter], Parameter[Parameter Fields] ),
        Parameter[Parameter]
    )
RETURN IF ( COUNTROWS ( __SelectedValue ) = 1, __SelectedValue )

If you want the complete list of the elements selected, you can use CONCATENATEX in the last line instead of the IF function:

VAR __SelectedValue = 
    SELECTCOLUMNS (
        SUMMARIZE ( Parameter, Parameter[Parameter], Parameter[Parameter Fields] ),
        Parameter[Parameter]
    )
RETURN CONCATENATEX ( __SelectedValue, Parameter[Parameter], ", " )

I hope this short post will be helpful to those who do not know the hidden details of the Tabular model!