Forum Discussion

vivek_babu's avatar
vivek_babu
Helper II
1 year ago
Solved

Single date input in Power BI

Hi,   I am trying to convert a spotfire dashboard to Power BI. We have a input field in Spotfire which takes datetime as the filter value so users type the required date in the input field then the...
Need Help
  • danextian's avatar
    danextian
    1 year ago

    Hi vivek_babu 

    Power BI doesn't currently support single date selection unless you're using a dropdown or tile slicer.   My usual workaround is to use a before option of the range slicer, a shape and a text box. The shape and textbox cover the min date of the slicer. They're then grouped and I ensure that maintain layer order is enabled for those three visuals

     

     

  • v-aatheeque's avatar
    v-aatheeque
    1 year ago

    Hi vivek_babu 

    To meet your goal allowing users to pick a single date easily (not from a scrollable range slicer), but through search or direct input you can use a slicer with dropdown

     

    Try this below steps :

    •  Add your date field to a slicer.
    • In the slicer settings >> slicer visual>> More options.
    • Choose “Dropdown” instead of “Between” or “List”. Click on the slicer where the dates are shown on your report.

     

    Reference :   https://databear.com/power-bi-on-screen-date-picker-no-custom-visual/
                        https://www.youtube.com/watch?v=zhWtU0DynCk&t=585s

     

    Hope this helps !!

    Thank You..

     

  • kushanNa's avatar
    kushanNa
    1 year ago

    Hmm, interesting. Is it possible for you to provide us with a sample scenario a PBIX file or a screenshot using some dummy data that you're facing the issue with? Also, have you used the TREATAS function in your scenario, like in Patrick's video? It seems that this function helps perform calculations for that specific date

kurrysamir's avatar
kurrysamir
Helper I
6 months ago

HI All,

 

I am working on a similar single date picker requirement where users want to select a date using a date picker. I am using direct query instead of Import mode. Each and every time the user selects a date, there is a stored procedure executes that has a date parameter and gets the data for that single date. Please correct my understanding here, the single date picker would work just with Import mode and not with a Direct Query?

  • v-aatheeque's avatar
    v-aatheeque
    Community Support
    6 months ago

    Hi kurrysamir 
    A single date slicer works in both Import and DirectQuery modes. The difference is how the data is handled when the date changes.

    • Import mode:
      Data is already loaded into Power BI. Selecting a date simply filters the cached data no new query is sent to the database.
    • DirectQuery mode:
      Data stays in the source. Each date selection sends a new query to the database with the date filter applied.

    In DirectQuery sends a query (or executes your stored procedure) each time.If your requirement is that every date selection must call a stored procedure with that date, then DirectQuery is the right choice. Import mode won’t do that because it doesn’t re-query the database after the initial load.

    • kurrysamir's avatar
      kurrysamir
      Helper I
      6 months ago

      Thank you for replying. I am following this thread in Power BI - Single Date Picker without DAX - Microsoft Fabric Community. Checked the youtube video, implemented the single date time picker. On selection of different dates, I am unable to pass the selected date paramterer to the function(its not a stored procedure, my bad for this). I have made a relationshop as 1..* from DateDimension table = 

      CALENDAR(DATE(2022,01,01), TODAY()-1) that follows a date hierarchy to the date column in the function. Any tutorial or link that I could follow, would be helpful.
      • v-aatheeque's avatar
        v-aatheeque
        Community Support
        6 months ago

        Hi kurrysamir,

        Thanks for sharing the details and references.

        The CALENDAR() expression you mentioned creates a DAX date table in Power BI, it is not a function in the source database. Creating a relationship between this Date table and a DirectQuery table will only apply a filter to the results returned from the source. It does not pass the selected date as a parameter to the source query.

         

        If your source function requires a date parameter, Power BI currently does not support binding slicer-selected values to function parameters in DirectQuery mode.