Slicer with Date/Time column not working

[lg01]

I have a table in Snowflake that contains a very large dataset. This data is coming from sesors reading in a scada system. They all have a Timestamp and I'd like to narrow the data using an "in between" slicer. I am connecting to the table using DirectQuery, so I only load the data I need. I have a slicer for my tags (which works as expected and narrows the data) and I need one for the Timestamp. I did the following:

1. Got a query from that Snowflake table with only the min and max Timestamp in the table, just one row of data and two columns. 

2. Created a calendar table using CalendarTable = CALENDAR(MIN('MyTable'[MinTimestamp]), MAX('MyTable'[MaxTimestamp])). This creates a calendar table with a Date/Time column

3. Create a Relationship to my Large Table as of 1:many (one from the CalendarTable table - many in the Snowflake table)

4. Create a slicer from CalendarTable column

5. Set up the dates in between 2 dates that I know contain data... and it comes back empty

What's going on? Is the slicer producing and passing Date instead of Date/Time? There is no error reported

[FBergamaschi · Accepted solution]

Hi,

no the slicer keeps the date/time format. I assume the column in the fact table is also date/time, right? If this is so, this should not happen, can you share a pbix via some cloud system? So I can help.

Thanks

[v-venuppu]

Hi @lg01 ,

I hope the information provided is helpful.I wanted to check whether you were able to resolve the issue with the provided solutions.Please let us know if you need any further assistance.

Thank you.

[v-venuppu]

Hi @lg01 ,

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 @lg01 ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @lbendlin @FBergamaschi for the prompt response.

I wanted to check if you had the opportunity to review the information provided and resolve the issue..?If not resolved can you please share the sample data and expected output as suggested by @FBergamaschi , so that it will be helpful for us to solve the issue.

Thank you.

[lbendlin · Accepted solution]

You need to add a column to your data that has the "Date"  value of your timestamp (ie the time pegged at 12:00 am midnight).  That's what the Calendar table needs for the matching.

Changing the format of the timestamp in Power BI is insufficient. Has to be a separate column.

[FBergamaschi · Accepted solution]

Hi,

no the slicer keeps the date/time format. I assume the column in the fact table is also date/time, right? If this is so, this should not happen, can you share a pbix via some cloud system? So I can help.

Thanks