Ranking a DAX table without exposing the Rank by column

ValeriaBreve — Fri, 22 Aug 2025 07:32:48 GMT

Hello!

I have a DAX code allowing me to output a table, which I use then in other systems (PowerAutomate) to communicate via mail to users.

It needs to be ranked, however it is super annoying to see the [Rank] column in the final mail table; and I had to use a [Rank] column, as the standard date column I would simply use needs to be formatted for later consumption (otherwise it gets the universal time format in Powerautomate and it becomes difficult to read).

Anyway, question is: is there a way to pre-order the final table, so that I can eliminate the column [Rank] from it but still have it ordered by [Unit] - which I want to keep - and [Rank]? I have been playing with it way too long, hence coming to the experts 🙂

I am leaving the whole code for completeness, I have highlighted the [Rank] parts in red, and the final table I am talking about is the one after the RETURN statement.

Thank you!

Kind regards

Valeria

EVALUATE

VAR _NumberofDaysIncluded = 4

VAR AllTable =

ADDCOLUMNS(

SELECTCOLUMNS(

SUMMARIZECOLUMNS(

SCHEDULE_UPLOAD_CHANGES_LATEST[PROD_ORDER_NUMBER],

SCHEDULE_UPLOAD_CHANGES_LATEST[FACILITY],

SCHEDULE_UPLOAD_CHANGES_LATEST[PRODUCT_NAME],

SCHEDULE_UPLOAD_CHANGES_LATEST[PRODUCT_CODE],

SCHEDULE_UPLOAD_CHANGES_LATEST[BATCH_NUMBER],

SCHEDULE_UPLOAD_CHANGES_LATEST[CHANGE_TYPE],

SCHEDULE_UPLOAD_CHANGES_LATEST[CHANGE_TYPE_DETAIL],

SCHEDULE_UPLOAD_CHANGES_LATEST[OLD_VALUE],

SCHEDULE_UPLOAD_CHANGES_LATEST[NEW_VALUE],

SCHEDULE_UPLOAD_CHANGES_LATEST[LAST_UPDATED_ON],

SCHEDULE_UPLOAD_CHANGES_LATEST[MODEL_CODE]

"Change Type", SCHEDULE_UPLOAD_CHANGES_LATEST[CHANGE_TYPE],

"Change Detail", SCHEDULE_UPLOAD_CHANGES_LATEST[CHANGE_TYPE_DETAIL],

"Old Value", SCHEDULE_UPLOAD_CHANGES_LATEST[OLD_VALUE],

"Old Date", IF(

SCHEDULE_UPLOAD_CHANGES_LATEST[CHANGE_TYPE_DETAIL] = "CHGDATE",

DATE((VALUE("20" & MID(

SCHEDULE_UPLOAD_CHANGES_LATEST[OLD_VALUE],

))),

(VALUE(MID(

SCHEDULE_UPLOAD_CHANGES_LATEST[OLD_VALUE],

))),

(VALUE(LEFT(

SCHEDULE_UPLOAD_CHANGES_LATEST[OLD_VALUE],

)))

) + TIME(

24,

BLANK()

"New Value", SCHEDULE_UPLOAD_CHANGES_LATEST[NEW_VALUE],

"New Date", IF(

SCHEDULE_UPLOAD_CHANGES_LATEST[CHANGE_TYPE_DETAIL] = "CHGDATE",

DATE(VALUE("20" & MID(

SCHEDULE_UPLOAD_CHANGES_LATEST[NEW_VALUE],

)),

VALUE(MID(

SCHEDULE_UPLOAD_CHANGES_LATEST[NEW_VALUE],

)),

VALUE(LEFT(

SCHEDULE_UPLOAD_CHANGES_LATEST[NEW_VALUE],

))

) + TIME(

24,

BLANK()

"PO", SCHEDULE_UPLOAD_CHANGES_LATEST[PROD_ORDER_NUMBER],

"Unit", SCHEDULE_UPLOAD_CHANGES_LATEST[FACILITY],

"GMID", SCHEDULE_UPLOAD_CHANGES_LATEST[PRODUCT_CODE],

"Product", SCHEDULE_UPLOAD_CHANGES_LATEST[PRODUCT_NAME],

"Batch", SCHEDULE_UPLOAD_CHANGES_LATEST[BATCH_NUMBER],

"Send to SAP Date/Time", FORMAT(

SCHEDULE_UPLOAD_CHANGES_LATEST[LAST_UPDATED_ON],

"dd/mm/yy HH:mm"

"Model", SCHEDULE_UPLOAD_CHANGES_LATEST[MODEL_CODE]

"Prod Date", LOOKUPVALUE(

Latest_Schedule[PRODUCTION_START],

Latest_Schedule[PROD_ORDER_NUMBER],

[PO]

"Prod Date Noon", DATE(YEAR(LOOKUPVALUE(

Latest_Schedule[PRODUCTION_START],

Latest_Schedule[PROD_ORDER_NUMBER],

[PO]

)), MONTH(LOOKUPVALUE(

Latest_Schedule[PRODUCTION_START],

Latest_Schedule[PROD_ORDER_NUMBER],

[PO]

)), DAY(LOOKUPVALUE(

Latest_Schedule[PRODUCTION_START],

Latest_Schedule[PROD_ORDER_NUMBER],

[PO]

))) + TIME(

24,

)

// Get the next 4 working days as a single column table from Date_Table starting today

VAR _NextWorkingDays =

SELECTCOLUMNS(

TOPN(

_NumberofDaysIncluded,

FILTER(

ALL(Date_Table),

Date_Table[Working Day] = TRUE &&

Date_Table[Date] >= TODAY()

Date_Table[Date],

ASC

"Date", Date_Table[Date]

)

// Filter AllTable so that the Prod Date is in one of the next 4 working days

VAR _FilteredTable =

FILTER(

AllTable,

[Prod Date Noon] IN _NextWorkingDays

|| [Old Date] IN _NextWorkingDays

|| [New Date] IN _NextWorkingDays

)

// Add a Rank column based on Prod Date in ascending order and format Prod Date as text

VAR RankedTable =

ADDCOLUMNS(

_FilteredTable,

"Rank", RANKX(

_FilteredTable,

[Prod Date],

ASC,

Dense

"Prod. Date", FORMAT(

[Prod Date],

"dd/mm/yy HH:mm"

"Delta Old/New", DATEDIFF(

[Old Date],

[New Date],

DAY

"Reprint?", IF(

ABS(DATEDIFF(

[Old Date],

[New Date],

DAY

)) >= 5 && [Change Detail] = "CHGQTY",

"X"

)

RETURN

SELECTCOLUMNS(

RankedTable,

[Unit],

[Change Type],

[Delta Old/New],

[Reprint?],

[Prod. Date],

[PO],

[Change Detail],

[Old Value],

[Old Date],

[New Value],

[GMID],

[Product],

[Batch],

[Model],

[Send to SAP Date/Time],

[Rank]

)

ORDER BY

[Unit],

[Rank] ASC

Re: Ranking a DAX table without exposing the Rank by column

wardy912 — Fri, 22 Aug 2025 08:46:45 GMT

Hi ValeriaBreve

Instead of using ORDER BY use TOPN with a very large number. TOPN returns the top N rows sorted by the specified columns.

RETURN SELECTCOLUMNS( TOPN( 100000, -- large number to include all rows RankedTable, [Unit], ASC, [Rank], ASC ), [Unit], [Change Type], [Delta Old/New], [Reprint?], [Prod. Date], [PO], [Change Detail], [Old Value], [Old Date], [New Value], [GMID], [Product], [Batch], [Model], [Send to SAP Date/Time] )

I hope this helps, please give kudos and mark as solved if it does, thanks!

Re: Ranking a DAX table without exposing the Rank by column

johnt75 — Fri, 22 Aug 2025 09:31:51 GMT

The argument for ORDER BY doesn't need to be in the table you're ordering, it can be a measure or an expression. Try

ORDER BY [Unit], DATEVALUE( [Prod. Date] ASC

Re: Ranking a DAX table without exposing the Rank by column

ValeriaBreve — Fri, 22 Aug 2025 11:21:20 GMT

johnt75 I love this solution! I don't know how I missed that you could use measures or expressions in ORDER BY. Thank you!!!!!

Re: Ranking a DAX table without exposing the Rank by column

ValeriaBreve — Fri, 22 Aug 2025 11:22:04 GMT

wardy912 This is another interesting solution! I learned something today :-). Thank you!!!

topic Re: Ranking a DAX table without exposing the Rank by column in DAX Commands and Tips

Ranking a DAX table without exposing the Rank by column

Re: Ranking a DAX table without exposing the Rank by column

Re: Ranking a DAX table without exposing the Rank by column

Re: Ranking a DAX table without exposing the Rank by column

Re: Ranking a DAX table without exposing the Rank by column