topic Re: Only Display Latest Sequence in DAX Commands and Tips

Only Display Latest Sequence

jmetf150 — Tue, 09 Apr 2024 16:59:10 GMT

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

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

Re: Only Display Latest Sequence

Greg_Deckler — Tue, 09 Apr 2024 17:13:57 GMT

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

Re: Only Display Latest Sequence

jmetf150 — Tue, 09 Apr 2024 17:56:52 GMT

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

Re: Only Display Latest Sequence

Greg_Deckler — Tue, 09 Apr 2024 17:57:26 GMT

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.

Re: Only Display Latest Sequence

jmetf150 — Tue, 09 Apr 2024 18:15:02 GMT

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

Re: Only Display Latest Sequence

Greg_Deckler — Tue, 09 Apr 2024 18:21:09 GMT

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.

Re: Only Display Latest Sequence

jmetf150 — Tue, 09 Apr 2024 21:27:53 GMT

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?

Re: Only Display Latest Sequence

Greg_Deckler — Wed, 10 Apr 2024 03:17:36 GMT

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