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'd like to add a few fields that calculate the Last Status based on the ID. That is, for the 'ID' = 54EBA584-0FDA-4766-B3B1-7FC0709C92BA the max 'Status Date' is 10/29/2018. So I would like a column populated with 10/29/2018 for all records with 'ID' = 54EBA584-0FDA-4766-B3B1-7FC0709C92BA.
Furthermore, and more importantly, I'd like a column that returns the latest 'Status Outcome' correlated to the MAX 'Status Date'. So for the same example a column with 'Last_Status' populated with ACTIVE for all records with 'ID' = 54EBA584-0FDA-4766-B3B1-7FC0709C92BA.
Is this possible to do in DAX?
ID Status Date Status Status Outcome
54EBA584-0FDA-4766-B3B1-7FC0709C92BA | 8/15/2018 | Pending | P01 |
54EBA584-0FDA-4766-B3B1-7FC0709C92BA | 8/20/2018 | Pending | P02 |
54EBA584-0FDA-4766-B3B1-7FC0709C92BA | 8/28/2018 | Pending | P03 |
54EBA584-0FDA-4766-B3B1-7FC0709C92BA | 9/3/2018 | Active | A01 |
54EBA584-0FDA-4766-B3B1-7FC0709C92BA | 10/1/2018 | Active | A01 |
54EBA584-0FDA-4766-B3B1-7FC0709C92BA | 10/29/2018 | Active | A01 |
162E0BD7-E850-4F9C-BA38-3540348A8778 | 8/22/2018 | Pending | P01 |
162E0BD7-E850-4F9C-BA38-3540348A8778 | 8/27/2018 | Pending | P02 |
162E0BD7-E850-4F9C-BA38-3540348A8778 | 9/4/2018 | Pending | P03 |
162E0BD7-E850-4F9C-BA38-3540348A8778 | 9/5/2018 | Cancelled | C05 |
8B876A64-6F52-48A5-8FF3-EBF3040DF15E | 8/24/2018 | Pending | P01 |
8B876A64-6F52-48A5-8FF3-EBF3040DF15E | 8/29/2018 | Pending | P02 |
8B876A64-6F52-48A5-8FF3-EBF3040DF15E | 9/8/2018 | Pending | P03 |
8B876A64-6F52-48A5-8FF3-EBF3040DF15E | 9/16/2018 | Active | A01 |
8B876A64-6F52-48A5-8FF3-EBF3040DF15E | 10/14/2018 | Active | A01 |
8B8716564-6F52-48A5-8FF3-EBF3040GD23 | 8/30/2018 | Pending | P01 |
3156fsd64-6F52-48A5-8FF3-EBF3040GD256 | 8/31/2018 | Pending | P01 |
3156fsd64-6F52-48A5-8FF3-EBF3040GD257 | 9/3/2018 | Pending | P01 |
3156fsd64-6F52-48A5-8FF3-EBF3040GD257 | 9/6/2018 | Pending | P02 |
3156fsd64-6F52-48A5-8FF3-EBF3040GD258 | 9/5/2018 | Pending | P01 |
Solved! Go to Solution.
Hi jkaufman,
To achieve your requirement, create two calculate column using DAX as below:
Max Status Date = CALCULATE(MAX(Table1[Status Date]), ALLEXCEPT(Table1, Table1[ID])) Result = CALCULATE(MAX(Table1[Status]), FILTER(ALLEXCEPT(Table1, Table1[ID]), Table1[Status Date] = Table1[Max Status Date]))
Regards,
Jimmy Tao
@jkaufman @sophy
https://community.powerbi.com/t5/Quick-Measures-Gallery/Power-BI-Get-the-Last-Latest-Value-of-a-Cate...
Hi jkaufman,
To achieve your requirement, create two calculate column using DAX as below:
Max Status Date = CALCULATE(MAX(Table1[Status Date]), ALLEXCEPT(Table1, Table1[ID])) Result = CALCULATE(MAX(Table1[Status]), FILTER(ALLEXCEPT(Table1, Table1[ID]), Table1[Status Date] = Table1[Max Status Date]))
Regards,
Jimmy Tao
I've been wracking my brain trying to figure out how to do this!! Thank you so much for your solution!
My code is pretty similar to yours, except I didn't use the Filter(AllExcept) function. I just used the following code. May I ask what does the Filter(AllExcept) does for this code, and why wouldn't it work without it?
Again, thank you so much.
CALCULATE(MAX(Table1[Status]), Table1[Status Date] = Table1[Max Status Date])
Thanks for your solution! Please help count people by the last status.
Hi,
Share some data, explain the question and show the expected result.
I faced a similar problem, but the solution failed with the external filter. This Doesn’t work when we apply an external or explicit filter on a Status date.
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 |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |