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.
Very new to DAX, this may be a simple task.
I have a table with Conversation ID, USerID, and Login TimeStamp. I am trying to show a trending chart of users, by Login TS.
Conversation ID | User ID | TimeStamp |
123 | Sam | March 10, 2020 23:45:00 |
123 | Sam | March 10, 2020 23:59:00 |
456 | Vimes | December 11, 2019 12:34:00 |
789 | Vimes | December 12, 2019 07:36:00 |
567 | Vimes | January 10, 2020 10:34:00 |
My calculation to show "New Users" requires me to identify the unique users who have had at least 30 hours expired since their last login. So, for the example above, Sam would not be considered a New User for March 10. Vimes would not be a new user for December 12 (since it has been less than 30 hours since his last login on December 11), but Vimes would be a new user for January 10. How can I achieve this? I was thinking of adding a calculated column with "Previous TS" for each user, and then take the difference between the two TS, but so far have been unable to achieve this.
Thank you!
Solved! Go to Solution.
A column for the hours since last login:
HoursSinceLastLogin = VAR _user = TableCons[User ID]
VAR _time = TableCons[TimeStamp]
RETURN
DATEDIFF( CALCULATE(MAX(TableCons[TimeStamp]), FILTER(TableCons, TableCons[User ID] = _user && TableCons[TimeStamp] < _time)), TableCons[TimeStamp] , HOUR)
A column for the hours since last login:
HoursSinceLastLogin = VAR _user = TableCons[User ID]
VAR _time = TableCons[TimeStamp]
RETURN
DATEDIFF( CALCULATE(MAX(TableCons[TimeStamp]), FILTER(TableCons, TableCons[User ID] = _user && TableCons[TimeStamp] < _time)), TableCons[TimeStamp] , HOUR)
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 |
---|---|
48 | |
27 | |
23 | |
13 | |
8 |
User | Count |
---|---|
73 | |
51 | |
45 | |
16 | |
12 |