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'm struggling to come up with the DAX to product a measure which will give me the "most recent" status for a product.
The report contains a date slicer, so the "most recent date" for the status would be dependent on the date range the user had filtered on.
My source data is one large table containing the following fields:
ProductID
ProductName
Location
Date
Status
ProductCategory
ProductAbbreviation
Many other dimensions which should be ignored for this report...
My desired output is a table with the following:
ProductID
ProductName
Location
Most recent status - new measure
Notes
- Data is added/updated monthly
- Products can be at more than one location
- Users will be able to filter on the date
The pbix model with some fake data in it is here:
https://mega.nz/file/ao1n3STS#uDqZD0TH-cG5-8_YlWu5PZSS0k6PVaaikXUtb4KSJxg
Solved! Go to Solution.
@alexei7 , Add this
lastnonblankvalue(Table[Date], max(Table[Status]))
or
calculate(max(Table[Status]), filter(Table, Table[Date] = calculate(max(Table[Date]), allexcept(Table, Table[Product ID]))))
or
calculate(max(Table[Status]), filter(Table, Table[Date] = calculate(max(Table[Date]), allexcept(Table, Table[Product ID], Table[Location]))))
@alexei7 , Add this
lastnonblankvalue(Table[Date], max(Table[Status]))
or
calculate(max(Table[Status]), filter(Table, Table[Date] = calculate(max(Table[Date]), allexcept(Table, Table[Product ID]))))
or
calculate(max(Table[Status]), filter(Table, Table[Date] = calculate(max(Table[Date]), allexcept(Table, Table[Product ID], Table[Location]))))
Thanks for your help - worked perfectly.
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 |
---|---|
110 | |
102 | |
86 | |
77 | |
68 |
User | Count |
---|---|
120 | |
108 | |
98 | |
83 | |
77 |