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.
Hi,
Can't figure this out:
I have a table(events) with 3 columns:
I want to return the DateTime of the last occurrence of the Status "Finished" per UserName.
Can anyone help?
Thanks!
govi
Solved! Go to Solution.
Create a new Calculated column and not Measure and add the following formula:
Rank = VAR d = 'Table'[DateTime] VAR a = 'Table'[UserName] RETURN CALCULATE ( RANK.EQ ( d, 'Table'[DateTime], DESC ), FILTER ( ALL ( 'Table' ), 'Table'[UserName]= a && 'Table'[Status]="Finished") )
Then create a new table object and on filter section put the rank field with value 1
Hi @govi
Try this:
1. Place Events[UserName] in the rows of a matrix visual
2. Place this measure in values of the matrix:
LastOccurrence = CALCULATE ( MAX ( Events[DateTime] ), Events[Status] = "Finished" )
Hi @govi
Try this:
1. Place Events[UserName] in the rows of a matrix visual
2. Place this measure in values of the matrix:
LastOccurrence = CALCULATE ( MAX ( Events[DateTime] ), Events[Status] = "Finished" )
Thanks @AlB !
Also a very useful solution.
I will use the solution @themistoklis provided for now because it is nice to have the returns in a column
Hi @govi
I am trying to figure out something VERY similar...
To use your example, this is as far as I have got:
=CALCULATE(
LASTDATE(DateTime),
FILTER(ALLEXCEPT(NameOfYourTable,UserName),Status="Finished"))
However, as you are probably also experiencing, it doesn't work...
The ALLEXCEPT UserName appears to be ignored, and the last Finished date is returned regardless of UserName.
Create a new Calculated column and not Measure and add the following formula:
Rank = VAR d = 'Table'[DateTime] VAR a = 'Table'[UserName] RETURN CALCULATE ( RANK.EQ ( d, 'Table'[DateTime], DESC ), FILTER ( ALL ( 'Table' ), 'Table'[UserName]= a && 'Table'[Status]="Finished") )
Then create a new table object and on filter section put the rank field with value 1
Hello! Hate to gravedig, but I'm getting a weird error with this forumla. It's ranking my entries in proper date order, and skipping *almost all* of my filter, but seems to be pushing the rank to lines with the same date. My running theory is that the formula sees that they're the same date (text) and just borks but I have no idea what I'm doing so who knows!
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 |
---|---|
113 | |
104 | |
77 | |
67 | |
63 |
User | Count |
---|---|
144 | |
107 | |
105 | |
82 | |
69 |