Slicer Options for Datetime Format Parameter

[cody_c]

I am building a report that uses three parameters for a SQL view in Direct Query mode: Start Date, End Date, and Site ID. The end goal is to allow the end user to select all three parameters to view their data on demand for the appropriate site and date range (based on work order target date).

The SQL query runs based on the defined Start Date and End Date, and the target dates in the data source are all in datetime format.  (All with time values of 00:00:00.)  I have brought in a date dimension table (DIMDATE) in Import mode, twice: binding one DATETIME field to the Start Date parameter, and binding the other DATETIME field to the End Date parameter.  Since the SQL view is in Direct Query mode, I am unable to convert any datetime values to date format, so the parameters are in datetime format.

Using three slicers for each of the three parameters, the report is functional.  However, it seems my slicer options for datetime values are extremely limited.  The only option I've found is manually scrolling through a dropdown list of the 7,000+ dates from the DIMDATE table - cumbersome, to say the least.  Since the value is not in date format, I can't use the calendar selector from the "Between" filter, and since it's not in text format, I can't add a search function to the slicer or even type the date I'm trying to select.  But the parameter has to be in datetime format for the SQL view to function. 

Has anyone encountered this before, or developed a workaround to make selecting a datetime value more user-friendly?  Thank you very much!  

[v-venuppu]

Hi @cody_c ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

[v-venuppu]

Hi @cody_c ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @Irwan @nielsvdc for the prompt response.

I wanted to check if you had the opportunity to review the information provided and resolve the issue..?Please let us know if you need any further assistance.We are happy to help.

Thank you.

[nielsvdc]

Hi @cody_c, the field used in the slicer does not have to be datetime as well. It can be a Date type, as long as it can be coerced to DateTime (00:00:00) when passed into the query. Because your underlying datetime data is always 00:00:00 anyway, this is safe. So in Power Query just change the type of the datetime column in the DIMDATE tables to type Date and your slicer filtering should keep working as it is now.

Hope this helps. If so, please give kudos 👍 and mark as Accepted Solution ✔️ to help others.

[Irwan · Accepted solution]

hello @cody_c 

i am not sure how your data looks like.

in my experience, since this is direct query, i did couple workaround to achieve the requirement.

here is an example..

1. 3fff... something is a direct query table.

2. then i summarize that direct query table. as you can see, the date is in datetime format.

3. then i created calculated column to simplify the datetime format into date format

4. create a relationship between those two table

5. plot into visual with summarize table as date slicer

i am not sure if this is a composite model, but here is what i did with direct query table and need to do a little change.

Hope this will help.

Thank you.

[cody_c]

Thank you, @Irwan, this looks promising!  For step 2, can you please elaborate on how/where you created that additional table summarizing the direct query table?

[Irwan]

hello @cody_c 

just create a new table in power bi.

Basically it is a local table that contained of direct query summarize.

if your pbix only has direct query, it will ask to create local model/table, just hit OK.

Hope this will help.

Thank you.