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,
In my table, I insert employee Id, Status and timestamp with Date. Status can be can be updated frequently (means it can be updated hourly).
How to identify latest Status on an employee on given Date?
Solved! Go to Solution.
Hi there
I got it working with the following measures below.
This first one is to get the Latest Status
Latest Status = CALCULATE(MAX('Data'[Datestamp]),ALLSELECTED('Data'[Datestamp]))
The next one is to get the Latest Status ID
Latest Status ID = CALCULATE(MAX('Data'[StatusID]),FILTER('Data','Data'[Datestamp] = MAX('Data'[Datestamp])))
And this is what it looks like when I created it in a table
Hi @vasu6811
What you could do, is to create a new measure which will be for the MAX of the Status.
For example:
Latest Status = MAX('TableName'[Status])
Hi guavaq,
I have tried your solution. Here problem is Status contains in different table. In employee transaction table, it contains only Status id. I have applied measure on status id. I am getting max of status id. But I expecting last updated status?
Here is sample data
EmployeeId | StatusID | Datestamp |
1 | 1 | 2017-05-01 11:30:00 |
1 | 3 | 2017-05-01 11:35:00 |
1 | 2 | 2017-05-01 11:40:00 |
2 | 3 | 2017-05-01 11:30:00 |
2 | 2 | 2017-05-01 11:35:00 |
2 | 1 | 2017-05-01 11:40:00 |
I am expecting result in following format
EmployeeId | StatusID | Datestamp |
1 | 2 | 2017-05-01 11:40:00 |
2 | 1 | 2017-05-01 11:40:00 |
Hi there
I got it working with the following measures below.
This first one is to get the Latest Status
Latest Status = CALCULATE(MAX('Data'[Datestamp]),ALLSELECTED('Data'[Datestamp]))
The next one is to get the Latest Status ID
Latest Status ID = CALCULATE(MAX('Data'[StatusID]),FILTER('Data','Data'[Datestamp] = MAX('Data'[Datestamp])))
And this is what it looks like when I created it in a table
Hi guavaq,
Thanks for solution. It worked for me.
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 |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |