How to Filter on Integer that is actually a timestamp?

[dittbub]

Hello (I may have already posted this but I can't find it),

I'm new to Power BI and I need some help.

I have a table that stores a timestamp (in minutes) as an integer type.  The table has an index on this column for fast searching.  In SQL I can convert a date to an integer and filter on that to get a result set.

But how can I accomplish that in power bi?  If I use the timestamp as a filter, it needs an integer which users won't know.  I want the user to select a date range as if its a datetime type but then have their selection converted to an integer.

I thought what if I select the timestamp integer as a datetime type.   I did that but then the powerbi filter is INCREDIBLY slow.  I verified in SQL that if I convert the timestamp to datetime the search isn't nearly as quick. it took minutes instead of seconds - and thats filtering just on one day.  This is intended as a monthly report.

Any help would be appreciated.

EG of a working select from SQL, filtering on Timestamp:

StartDate = '2019-01-01'

EndDate = '2019-01-31

SELECT *
FROM [Table]
WHERE [Timestamp] >= DATEDIFF(minute,'1899-12-31',@StartDate)
AND [Timestamp] <= DATEDIFF(minute,'1899-12-31', @EndDate)

[TomMartens]

Hey,

I assume you use the DirectQuery mode to connect to your SQL table.

Create another table in SQL database that just contains unique values for the integer representing the data. Add some "readable" columns like date, YYYY-MM, YYYY ... to this table, let's call it calendar.

Add this table to your datamodel inside Power BI.

Create a relationship between this new Calendar table (the one side of the relationship) and your existing table (on the many side), use the integer column representing the date for the relationship.

Hide the integer column from the Calendar table, I would also recommend to hide this column from your already existing table.

Use the Calendar table to filter the table [Table].

Regards,

Tom 

[dittbub]

This might be way over my head....  but are you suggesting I store a row for every minute between the earliest possible reporting time to the foreseeable reporting future?

[TomMartens]

Hey,

basically this is my suggestion, but the granularity of the new Calendar table is defined by the granularity of the rows in your "fact"table.

If you need the time portion, then you also need the minute portion in the calendar table. If you don't need the time portion you may ask your sql developers to add anohter column to your fact table that whith a new integer that represent a day this way: 2019-02-09 12:00AM.

Then also ask your SQL developers to create the calendar table with the needed columns.

Regards,

Tom