Forum Discussion
Advocate IIISlicer with Date/Time column not working
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
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
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.
5 Replies
- FBergamaschi
Super User
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
- lbendlin
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.
v-venuppuCommunity Support
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.