Retrieve last record based on 2 sortings

[zanottiluca]

Hi guys, 

I am struggling with a DAX computation: in brief I would like to retrieve the latest record in a group by two sorting order. The first is based on the Invoice number descending, but if within the same group there are no invoiced then use the latest order update. (Modified date). 
It is something that in Oracle can be achieved in this way:
select * from MV_SOH where "Sales Order" in (
(SELECT DISTINCT
MAX("Sales Order") KEEP (DENSE_RANK FIRST ORDER BY "Invoice ID" desc nulls last , "Modified Date" desc ) OVER (PARTITION BY "Group") ranked

FROM MV_SOH
))

I wonder if this can be achievable in DAx which seems more performant.

And the data can be the follwoing (further below the one that should be retrieved).

Groupid	Order	InvoiceN	Modified Date
1	SOR12	IN88	01/01/2019 15:03
1	SOR13	IN87	01/02/2019 16:03
1	SOR14		02/02/2019 17:00
1	SOR5		03/02/2019 18:00
2	SOR8		04/02/2019 15:00
2	SOR9		05/02/2019 16:00
2	SOR10		06/02/2019 11:00

The retrieved are 

Groupid	Order	InvoiceN	Modified Date
1	SOR12	IN88	01/01/2019 15:03
2	SOR10		06/02/2019 11:00

Many thanks for your help

Luca

[Zubair_Muhammad · Accepted solution]

@zanottiluca 

try this as Calculated Table

Calculated Table = 
GENERATE (
SELECTCOLUMNS ( VALUES ( 'Table1'[Groupid]), "Groupid_", [Groupid] ),
CALCULATETABLE ( TOPN ( 1, 'Table1', [InvoiceN], DESC,[Modified Date],DESC ) )
)

[zanottiluca]

Hi, 

@Zubair_Muhammad . many thanks for this it worked and you opened a new "world" about calculated tables.

Many thanks for this

Luca

---

@Zubair_Muhammad wrote:

@zanottiluca 

try this as Calculated Table

Calculated Table = 
GENERATE (
SELECTCOLUMNS ( VALUES ( 'Table1'[Groupid]), "Groupid_", [Groupid] ),
CALCULATETABLE ( TOPN ( 1, 'Table1', [InvoiceN], DESC,[Modified Date],DESC ) )
)

---

---

@Zubair_Muhammad wrote: