Row Level Security → Default Slicer Selection
One more experiment with the Button Slicer: Row-Level Security (RLS)-based default slicer selection (for cases where security isn’t required but a default selection is desired).
In this example, I demonstrate how the currently logged-in user can be selected by default in the Button Slicer. See the video: the logged-in employee always appears at the top of the list, selected by default and highlighted in red font, while other employees are listed in alphabetical order.
Solution
It’s based on a similar approach to Power BI – Date Slicer with Default Selection. The Button Slicer maintains the default selection using the Value (which is constant), while the user sees the Label.
First of all, I created an Employee Slicer table as a copy of the Employee table. The Employee Slicer table includes all employees twice: the first time with the Employee ID (Index column) in the Selection column, and the second time with 0 in the Selection column.
Row-Level Security (RLS) is used to remove duplicate employees from the table. We keep only one record for the selected (logged-in) employee – the record with 0 in the Selection column. For all unselected employees (those other than the logged-in employee), we keep the records with non-zero values in the Selection column.
( 'Employee Slicer'[Email] == USERPRINCIPALNAME() && 'Employee Slicer'[Selection] == 0 ) ||
( 'Employee Slicer'[Email] <> USERPRINCIPALNAME() && 'Employee Slicer'[Selection] <> 0 )Employee Slicer table when isa57@test.org user logged in:
Employee Slicer table when sop68@test.org user logged in:
The Selection column contains a list of employee IDs, but the ID of the active (logged-in) user is always 0. This is how the default selection always points to the active user: the default selection is the user with 0 in the Selection column.
In the data model, the Employee Slicer table is related to the Employee table by the Index column (cross-filter direction: both). This ensures that whatever is selected in the slicer will also be selected in the Employee dimension table.
Note: two-directional relationships are useful when you know where and why to use them. Otherwise they are dangerous.
Now we can create add Button Slicer. The Selection column goes into the Value field of the slicer.
Now select “0” value in the slicer.
Disable displaying Values and enable displaying Labels. The Name column goes into the Label field of the slicer.
Make sure currently logged in user is selected.
Button Slicer = “wasted space”?
The Button Slicer takes up a lot of space on the report page. Unfortunately, despite the new Button, Text, and List slicers being available, the old drop-down slicer still lacks new formatting options, such as separate values and labels. However, we can already repurpose the Button Slicer into a useful chart, and the problem of “wasted space” is solved.
Example of how we can turn the slicer into an interactive IBCS-styled chart:
P.S. The real solution for default selection, measure-driven selection, doesn’t yet exist.
PBIX file: