The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi,
I'm trying to write a measure which will calculate the latest status of an item , by location and by the maximum date applicable. Sample data as per below. Any guidance would be much appreciated.
thanks
Scott
Solved! Go to Solution.
@PaisleyPrince This seems to work for me. See PBIX attached beneath signature:
Latest Status =
VAR __LatestDate = MAX('Table'[Date])
VAR __Result = MAXX(FILTER('Table',[Date] = __LatestDate), [Status])
RETURN
__Result
Hi @Greg_Deckler , I've tried it but it doesnt work as expected. For example Item 2 - S01 gives the wrong result. Sample data in text is as follows
Item | location | Status | Date |
1 | S01 | Current | 01/02/2024 |
1 | S02 | Discount | 01/03/2023 |
1 | S03 | Notrequest | 22/07/2023 |
1 | S01 | Discount | 30/11/2023 |
1 | S02 | Notrequest | 01/07/2023 |
1 | S03 | Current | 18/05/2022 |
2 | S01 | Current | 17/02/2022 |
2 | S02 | Notrequest | 01/12/2023 |
2 | S03 | Discount | 01/12/2023 |
2 | S01 | Discount | 15/06/2023 |
2 | S02 | Current | 22/03/2024 |
2 | S03 | Notrequest | 22/01/2024 |
3 | S01 | Discount | 11/02/2023 |
3 | S02 | Notrequest | 11/02/2024 |
3 | S03 | Current | 11/05/2023 |
3 | S01 | Current | 16/05/2023 |
3 | S02 | Discount | 12/03/2024 |
3 | S03 | Discount | 12/02/2023 |
@PaisleyPrince This seems to work for me. See PBIX attached beneath signature:
Latest Status =
VAR __LatestDate = MAX('Table'[Date])
VAR __Result = MAXX(FILTER('Table',[Date] = __LatestDate), [Status])
RETURN
__Result
@PaisleyPrince Lookup Min/Max - Microsoft Fabric Community
@Greg_Deckler , thanks for this pattern. My only question is that i'm looking up a status rather than a numerical value - can you please advise how would i amend the pattern accordingly?
@PaisleyPrince Is largely the same. Can't test because you didn't provide your sample data in text and no idea about your visual but should be something like:
Measure =
VAR __Item = MAX('Table'[Item])
VAR __Location = MAX('Table'[Location])
VAR __Table = FILTER( ALLSELECTED('Table'), [Item] = __Item && [Location] = __Location )
VAR __MaxDate = MAXX( __Table, [date] )
VAR __Result = MAXX( FILTER( __Table, [date] = __MaxDate ), [Status] )
RETURN
__Result
User | Count |
---|---|
25 | |
12 | |
8 | |
8 | |
7 |
User | Count |
---|---|
28 | |
13 | |
12 | |
12 | |
6 |