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.
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],
7,
2
))),
(VALUE(MID(
SCHEDULE_UPLOAD_CHANGES_LATEST[OLD_VALUE],
4,
2
))),
(VALUE(LEFT(
SCHEDULE_UPLOAD_CHANGES_LATEST[OLD_VALUE],
2
)))
) + TIME(
24,
0,
0
),
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],
7,
2
)),
VALUE(MID(
SCHEDULE_UPLOAD_CHANGES_LATEST[NEW_VALUE],
4,
2
)),
VALUE(LEFT(
SCHEDULE_UPLOAD_CHANGES_LATEST[NEW_VALUE],
2
))
) + TIME(
24,
0,
0
),
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,
0,
0
)
)
// 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
