Forum Discussion
Only Display Latest Sequence
Goodday - I am looking for the DAX code/formula I can use to do the following. I have a list of Open PO numbers. Each open PO can have up to 9 different confirmation types. Each confirmation type results in a seperate line and I only want to show the PO line with the latest confirmation.
The different confirmations are below in sequence:
blank or null = not confirmed yet
AB
P2
E1
E2
E3
E4
E5
SN
Example of my data:
| PO | Ord Qty | Confirmation Type | Confirmation Seq | Confirmation Date |
| 450123 | 10 | AB | 2 | 6/22/24 |
| 450123 | 10 | P2 | 3 | 7/10/24 |
| 450123 | 10 | E1 | 4 | 9/5/24 |
| 450123 | 10 | E2 | 5 | 10/31/24 |
| 450123 | 10 | E3 | 6 | 11/2/24 |
| 450123 | 10 | E4 | 7 | 11/30/24 |
| 450123 | 10 | E5 | 8 | 12/1/24 |
| 450123 | 10 | SN | 9 | 12/25/25 |
| 450123 | 10 | 1 |
jmetf150 You can use a Complex Selector for this. The Complex Selector - Microsoft Fabric Community
Probably something like:
Measure = VAR __PO = MAX('Table'[PO]) VAR __CurrentDate = MAX('Table'[Confirmation Date]) VAR __MaxDate = MAXX( FILTER( ALL('Table'), [PO] = __PO ), [Confirmation Date]) VAR __Result = IF( __CurrentDate = __MaxDate, 1, 0 ) RETURN __Resultjmetf150 No, because they don't alphabetically sort correctly. So if you had AB, P2 and E1 and got the max, it would return P2. You could create a calculated column that assigned a number such as a SWITCH statement that assigned 1 to AB, 2 to P2, etc. and that should work in lieu of a date.
7 Replies
Greg_Deckler forgot I already had a column that told me the sequence number for that line so just modified the "date" to the "confirmation sequence number" and filtered to only show me "1" and it worked.
Now, how would I apply that meansure to a card?
jmetf150 Well, that's a bit tricky TBH as I am not sure what you are trying to display in that card. But, if you have that Measure, you could do this:
Card Measure = VAR __Table = ADDCOLUMNS('Table', "__Measure", [Measure]) VAR __Row = FILTER(__Table, [__Measure] = 1) VAR __ConfirmationType = MAXX( __Row, [Confirmation Type]) RETURN __ConfirmationType
jmetf150 You can use a Complex Selector for this. The Complex Selector - Microsoft Fabric Community
Probably something like:
Measure = VAR __PO = MAX('Table'[PO]) VAR __CurrentDate = MAX('Table'[Confirmation Date]) VAR __MaxDate = MAXX( FILTER( ALL('Table'), [PO] = __PO ), [Confirmation Date]) VAR __Result = IF( __CurrentDate = __MaxDate, 1, 0 ) RETURN __ResultGreg_Deckler To simplify this for me, how would this look if you only had 2 columns. PO & Confrimation type.
jmetf150 Then you wouldn't have anything to define "latest" so it wouldn't work unless you added like an Index column assuming that things are actually sorted correctly.
