cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## Retrieve last record based on 2 sortings

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

Luca

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Retrieve last record based on 2 sortings

try this as Calculated Table

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

## Re: Retrieve last record based on 2 sortings

try this as Calculated Table

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

## Re: Retrieve last record based on 2 sortings

Hi,

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

Many thanks for this

Luca

try this as Calculated Table

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