cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
zanottiluca Regular Visitor
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).

GroupidOrderInvoiceNModified Date
1SOR12IN8801/01/2019 15:03
1SOR13IN8701/02/2019 16:03
1SOR14 02/02/2019 17:00
1SOR5 03/02/2019 18:00
2SOR8 04/02/2019 15:00
2SOR9 05/02/2019 16:00
2SOR10 06/02/2019 11:00

 

The retrieved are 

GroupidOrderInvoiceNModified Date
1SOR12IN8801/01/2019 15:03
2SOR10 06/02/2019 11:00




Many thanks for your help

 

Luca

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Retrieve last record based on 2 sortings

@zanottiluca 

 

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
Super User

Re: Retrieve last record based on 2 sortings

@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 Regular Visitor
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


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