CODEWITHSUNDEEP
sql-serverpowerbidax
James Khan
James Khan

Reputation: 841

Power BI Time Difference

I have a date a user was invited to join an application in the ApplicationUser Table, called DateInvited. I also have a column that stores the time element of the invite(TimeInvited). I have another table called ApplicationAccessLog that contains a date for the access ( Access Date) and a seperate column for the time id, which is a time key (TimeID) linked to a DimTime table, which then gives tyou the time element.

-ApplicationUser Table ( Not linked to DimTime)

( DateInvitedID ( INT) , DateInvited ( DateTime) , TimeInvited(Time) )

-ApplicationAccessLog ( Linked to DimTime via TimeID)

( AccessDate ( Date) , TimeID ( INT) ) 

-DimTime 

TimeId  int
TimeValue   time
DayTimeText char
FlagMorning bit
Hour    tinyint
MinuteDay   smallint
MinuteHour  tinyint
SecondDay   int
SecondHour  smallint
SecondMinute    tinyint

Sample Data

App User Name   Access Date     TimeId  Hour    MinuteHour  SecondMinute    TimeValue   DateInvited TimeInvited
Mr x    24/02/2020  32222   8   57  2   08:57:02    23/02/2020 17:20    17:20:05

I need to work the time between in hours of the date invited and the First Access Date. So the concepts of seperate time and date colmns in power bi has thrown me. Should I convert this to a combined date time in the Datawarehouse and then do a simple DATEDIFF in the power bi side or power query ?

Is there a simple way to do this in DAX and avoid introducing a combined date and time column ?

Upvotes: 0

Views: 1267

Answers (1)

Giovanni Luisotto
Giovanni Luisotto

Reputation: 1400

To me, the easiest solution is to make the DateTime column either in the DWH or in power query (or in DAX). (better if done in power query) If you don't "materialize" the column, it will be re-calculated every time the expression is called and I'm not sure about how the performance can be. (it's up to the number of rows). You can also set this column as hidden if you don't want it to be seen.

--In Power Query use this function as column expression
#datetime(year as number, month as number, day as number, hour as number, minute as number, second as number)

--Using Dax Calculated Column
DateTimeCol = DATE ( YYYY, MM, DD ) + TIME ( hh, mm, ss )

--in a measure, you can then use the DATEDIFF function (or in the calculated column itself if you prefer)

Upvotes: 1

Related Questions