Dynamic Slicer for selecting alphabetically first name by default

[sups7302]

Hi All,

In my dashboard which is secured using RLS i have a Name Filter which can have different set of names for every user. i want power BI to select very first name (alphabetically) everytime user opens the dashboard.

Is this even possible? i checked posts but everything related to dynamic slicers are based on measures or dates.

[v-jtian-msft · Accepted solution]

Hi,MAwwad ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.

Hello,@sups7302 .I am glad to help you.

I created a simple example that I hope will give you some good ideas.
Here is my test data.

You need to create a sort sequence for the fields used by the slicer you want to sort and set the sort for them.

configure RLS

View AS RLS_Role
 

The RLS was successfully set and the slicer fields were successfully sorted (alphabetically) according to the set sorting sequence.
Sort by alphabetical order

This is my test code, hope it helps.
You need to write your own sorting logic (based on which fields are sorted according to what judgment logic, etc.), create a correct sorting sequence, and make sure that the RLS is set up correctly, and that the Role is assigned correctly after uploading it to the Power BI Service.

M code:

let
    Source = Sql.Database("vm0", "AdventureWorksDW2022"),
    dbo_RLSGroup = Source{[Schema="dbo",Item="RLSGroup"]}[Data],

    // The following steps create the sorting sequence to ensure that the slicers are in the correct order, you need to write your own sorting logic based on the actual sorting situation. 
    // Group by GroupID
    GroupedRows = Table.Group(dbo_RLSGroup, {"GroupID"}, {{"GroupedData", each _, type table [UserID=Int64.Type, NickNames=Text.Type, GroupID=Int64.Type, ProjectNames=Text.Type]}}),

    // Sort by NickNames within each group and add index columns
    AddIndex = Table.TransformColumns(GroupedRows, {"GroupedData", each Table.AddIndexColumn(Table.Sort(_, {{"NickNames", Order.Ascending}}), "Index", 1, 1, Int64.Type)}),
    #"Expanded GroupedData" = Table.ExpandTableColumn(AddIndex, "GroupedData", {"UserID", "NickNames", "ProjectNames", "UPN", "Index"}, {"GroupedData.UserID", "GroupedData.NickNames", "GroupedData.ProjectNames", "GroupedData.UPN", "GroupedData.Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded GroupedData",{{"GroupedData.UserID", "UserID"}, {"GroupedData.NickNames", "NickNames"}, {"GroupedData.ProjectNames", "ProjectNames"}, {"GroupedData.UPN", "UPN"}, {"GroupedData.Index", "SortIndex"}})
in
    #"Renamed Columns"

I have uploaded the corresponding pbix file, hopefully it will give you good ideas.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MAwwad]

Yes, it is possible to set a default selection in a Power BI slicer based on the first name alphabetically for each user. However, this requires a bit of a workaround since Power BI does not natively support dynamic default slicer selections based on row-level security (RLS).

Here’s a step-by-step approach to achieve this:

1. Create a Calculated Column for Alphabetical Order:

   • Add a calculated column to your dataset that assigns a rank to each name based on alphabetical order.

     NameRank = RANKX(ALL('YourTable'[Name]), 'YourTable'[Name], , ASC, DENSE)

   • Create a Measure to Get the First Name:

     • Create a measure that returns the first name alphabetically for the current user.

       FirstName = 
       CALCULATE(
           FIRSTNONBLANK('YourTable'[Name], 1),
           FILTER(
               'YourTable',
               'YourTable'[NameRank] = 1
           )
       )

     • Create a Visual Level Filter:

       • Use the measure created in the previous step as a visual level filter for your slicer.

       • Set the Default Selection:

         • Unfortunately, Power BI does not allow setting a slicer default value dynamically. However, you can use bookmarks and buttons to simulate this behavior:
           • Create a bookmark with the slicer set to the first name alphabetically.
           • Add a button that users can click to reset the slicer to this default state.

           • Implement Row-Level Security (RLS):

             • Ensure your RLS roles are properly set up so that each user only sees the relevant names in the slicer.

               Here’s a more detailed example:

               1. Calculated Column:

                  NameRank = RANKX(ALL('YourTable'[Name]), 'YourTable'[Name], , ASC, DENSE)

               2. Measure:

                  FirstName = 
                  CALCULATE(
                      FIRSTNONBLANK('YourTable'[Name], 1),
                      FILTER(
                          'YourTable',
                          'YourTable'[NameRank] = 1
                      )
                  )

               3. Visual Level Filter:

                  • Apply the FirstName measure as a filter to the slicer visual.

                  • Bookmarks and Buttons:

                    • Create a bookmark with the slicer set to the first name.
                    • Add a button that users can click to reset the slicer to this bookmark.

                      This approach ensures that each user sees the first name alphabetically in the slicer based on their RLS context, and they can reset the slicer to this default state using a button.