Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
try this as Calculated Table
Calculated Table = GENERATE ( SELECTCOLUMNS ( VALUES ( 'Table1'[Groupid]), "Groupid_", [Groupid] ), CALCULATETABLE ( TOPN ( 1, 'Table1', [InvoiceN], DESC,[Modified Date],DESC ) ) )
try this as Calculated Table
Calculated Table = GENERATE ( SELECTCOLUMNS ( VALUES ( 'Table1'[Groupid]), "Groupid_", [Groupid] ), CALCULATETABLE ( TOPN ( 1, 'Table1', [InvoiceN], DESC,[Modified Date],DESC ) ) )
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:
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:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |