Forum Discussion

LCHHATRIA's avatar
LCHHATRIA
New Member
11 days ago

Dynamically auto-select the smallest value in a single-select slicer while displaying all values?

Hi Community,

I'm looking for a solution to dynamically set a default value in a Power BI slicer.

Requirement

  • I have a single-select dropdown slicer based on Order ID.
  • Order ID is a text field.
  • The slicer should display all available Order IDs.
  • When the report is opened, I want the slicer to automatically select the smallest/oldest Order ID(which was created at first) available in the dataset.
  • Users should then be able to select any other Order ID from the dropdown.

Constraints

  • The report is deployed across multiple workspaces/environments.
  • The Oldest Order ID differs between workspaces.
  • Therefore, a bookmark with a fixed Order ID is not a viable solution.
  • The selection needs to be dynamic based on the data available in the semantic model.

What I've Tried

  • Bookmarks (works only for fixed values).
  • DAX measures to determine the Oldest Order ID.
  • Measure-driven visual filtering.

However, I have not found a way to bind the slicer's selected value to a DAX measure and dynamically select the Oldest Order ID on report load.

Question

Is there any supported approach to dynamically pre-select the Oldest Order ID in a native Power BI single-select slicer while still displaying all Order IDs in the dropdown?

Any guidance, workarounds, or custom visual recommendations would be greatly appreciated.

Thanks!

#PowerBI #PowerBIDesktop #PowerBIService #Slicer #DAX #Bookmarks #SemanticModel #DataModeling #ReportDesign #UX

Need Help
Tips and Tricks

7 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    11 days ago

    Hi LCHHATRIA​ ,

    As you mention there is no way to show the lowest value by default, this question is often presented when refereeing about the last available date for example, the workaround that usually is used for Dates is to have a new column with the values of the dates and then for the current date instead of the date have the text "Current Date" that way when you select the default value on the slicer it will always go back to the specified value.

    Not sure if this can help on your case since you would get not the number of the order but some text, but if you want to implement this you need to generate a new  column in Power Query using this code

    if [Order ID] = List.Min (Source[Order ID]) then "Lowest ID" else [Order ID]

    This will return the following table:

    Then just use the sort by column on this new column and use it on your slicer making the default the Lowest ID.

    Regards

    Miguel Félix


    Did I answer your question? Mark my post as a solution!

    Proud to be a Super User!

    Check out my blog: PBI Portugal

    • LCHHATRIA's avatar
      LCHHATRIA
      New Member
      11 days ago

      Hi MFelix​,

      Thanks for responding to our query. we did try the Power Query approach that you have suggested above but in our case we are using the Direct Query connection. so we are not able to create the column using power query approach as mentioned below.

      if [Order ID] = List.Min (Source[Order ID]) then "Lowest ID" else [Order ID]

      Is there any other way we can achieve while using the direct query mode itself? Please let us know.

      Thanks!!

       

      • tayloramy's avatar
        tayloramy
        Icon for Super User rankSuper User
        11 days ago

        Hi LCHHATRIA​,

        If you're using a Direct Query connection, options are very limited for transformation in Power BI itself. 

        Can you modify your source query to add the column MFelix​ described? Or are you able to create a view on the source side to add this column in?  

  • RossEdwardsRio's avatar
    RossEdwardsRio
    Frequent Visitor
    10 days ago

    Here is a suggestion for you.  Make a 2nd column (dax calculated column) which will update during refresh.  Make this column a string where its contents are the ID column its duplicating. The only difference is you have a calculation that identifies what the MIN record is and if the current row is the MIN row, instead of recording the ID itself, you record some pre-defined text like "Default ID".

    I've often used a similar concept in Date Tables where i mark the "Reporting Month" or "Current Month" so the date selection can be a set rolling period that is data driven.

    Since the new field has a set string, once you put that into your slicer Power BI will remember that text. Its meaning is now dynamic.

  • v-csrikanth's avatar
    v-csrikanth
    Icon for Community Support rankCommunity Support
    9 days ago

    Hi LCHHATRIA​ 

    We would like to inquire whether have you got the chance to check the solutions provided by other users in community to resolve the issue. We hope the information provided helps to clear the query. Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.

  • v-csrikanth's avatar
    v-csrikanth
    Icon for Community Support rankCommunity Support
    4 days ago

    Hi LCHHATRIA​ 
    We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

    Thank you.