I am trying to produce a daily chart for showing a measure ( RepeatTimeToAcesss) that tells you how long it took for a user to access and application again, against the day of the week on the Y Axis. Now the Tuesday value ( today) works out but the rest of the week does not in the chart.
_RepeatTimeToAccess_Daily_Value =
var allseconds=
CALCULATE (
AVERAGEX(
VALUES(AppUser[UserId]),
DATEDIFF(IF(AppUser[LastLogin]=DATE(1899,12,30),blank(),AppUser[LastLogin]),AppUser[CurrentDate],SECOND)
),
FILTER('Date','Date'[DateId]<=SELECTEDVALUE('DateSelector'[DateId],MAX('DateSelector'[DateId])) )
)
VAR days =
INT ( allseconds / 24 / 60 / 60 )
RETURN
days
& " hours "
Average Repeated Time to access for all users regardless if they logged in today. The above measure excludes any user that never logged in by the date exclusion if statment. This works perfectly for an individual day selected using a disconnected Date Selector table on the HOME power BI page.
Dashboard on Direct Query mode, so relevant to dax options here.
To explain this currentdate measure, I for today take the time right now as the current date if today is selected. If it yesterday the measure is being calculated for then it is yesterday date at 23.59.59 that will be used as the current date.
CurrentDate =
var DateSelected=cALCULATE(max('Date'[DayDate]),FILTER('Date',SELECTEDVALUE('DateSelector'[DateId],MAX('DateSelector'[DateId])) = 'Date'[DateId]))
var Today=TODAY()
RETURN
IF ( Today=DateSelected,Now(),datevalue(DateSelected)+ TIME(23,59,59)
)
Sample Data Output when I try to produce a chart for measure against DayNameShort from connected DATE table.
DayNameShort( Date table) RepeatTimeToAcesss ( Hrs )
Mon -43891
Tues 13 hrs ( Correct ! )
Wed -43888
Thurs -43888
Frid -43889
Sat -43891
Sun -43891
I see 3 options to get the chart working. I want to get option 3 to work and need some insight on that approach.
Option 1 - Build a summary table in sql to show
Day / RepeatTimeToAcesss
Option 2 - Build a sumarized table in DAX
With Day / RepeatTimeToAcesss
Option 3 - Get the DAX to work with current Data Model
