Only Display Latest Sequence

[jmetf150]

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

[Greg_Deckler · Accepted solution]

@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
  __Result

[jmetf150]

@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?

[Greg_Deckler]

@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

[Greg_Deckler · Accepted solution]

@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
  __Result

[jmetf150]

@Greg_Deckler  To simplify this for me, how would this look if you only had 2 columns.  PO & Confrimation type.

[Greg_Deckler]

@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.

[jmetf150]

@Greg_Deckler  Can you use the confirmation sequence to define the latest?  blank/null would always be first, and SN would always be last (per the sequence defined below)

The different confirmations are below in sequence:

blank or null = not confirmed yet

AB

P2

E1

E2

E3

E4

E5

SN

[Greg_Deckler · Accepted solution]

@jmetf150 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.