Forum Discussion

zanottiluca's avatar
zanottiluca
Icon for Helper II rankHelper II
7 years ago
Solved

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

 

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    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

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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's avatar
      zanottiluca
      Icon for Helper II rankHelper II
      7 years ago

      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: