Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Hi @Anonymous ,
Could you please share a PBIX file with dummy data?(using OneDrive for Business)
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Any update on this ? This is becoming urgent for me.
https://drive.google.com/open?id=1m2OhpKR-5JIsxVr6WdfX1obOR1iQZdcJ
Please download it and I will close the Share. This is mock data so no real data but shows the problem well.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |