topic Re: Rankx function showing all 1 in a calculated column in DAX Commands and Tips

Rankx function showing all 1 in a calculated column

AlexPayette — Fri, 21 Oct 2022 13:39:01 GMT

Hi,

I am trying to use the Rankx function to order my different shifts starting with 1 desc. I have a fact table which showcases my shifts everyday. There's two shifts, day shift (from 6h AM to 5:59:59 PM) and night shift (from 6h PM to 5:59:59).

When using the Rankx function, I am only obtaining all 1 or 1 and big numbers (passing from 1 to 512, etc.)

I would like to use this function to rank my different shifts, starting from the most recent shift (as 1). I have a fact table containing multiple rows for each shifts, and I woud like to be able to rank the shift based on the shift (start and end dates).

This is what I am trying to use as a function :

=RANKX(
FILTER(
FactTable,
[Shift Start Date] = EARLIER('Table'[Shift Start Date)
&&
[Shift End Date] = EARLIER('Table'[Shift End Date)
),
[FactTable].[Date Dump],
,
DESC,
Dense)

This is what I would like my fact table to look like :

RefDateLoad	RefDateDump	Date Dump	Date Time Dump	Shift Start Date	Shift End Date	Rank	Last 2 shifts	Quantity
20220928	20220928	2022-09-28	2022-09-28 6:02:09	2022-09-28 06:00:00	2022-09-28 18:00:00	1	TRUE	1000
20220928	20220928	2022-09-28	2022-09-28 11:10:11	2022-09-28 06:00:00	2022-09-28 18:00:00	1	TRUE	2000
20220927	20220927	2022-09-27	2022-09-27 18:30:47	2022-09-27 18:00:00	2022-09-28 06:00:00	2	TRUE	3000
20220927	20220927	2022-09-27	2022-09-27 21:45:58	2022-09-27 18:00:00	2022-09-28 06:00:00	2	TRUE	4000
20220927	20220927	2022-09-27	2022-09-27 12:22:22	2022-09-27 06:00:00	2022-09-27 18:00:00	3	FALSE	5000

Re: Rankx function showing all 1 in a calculated column

amitchandak — Fri, 21 Oct 2022 15:02:15 GMT

AlexPayette , You have use date time dump column

Column = RANKX(FILTER(Data, Data[Shift End Date] =EARLIER([Shift End Date]) && Data[Shift Start Date] = EARLIER([Shift Start Date])), [Date Time Dump],,DESC,Dense)

Re: Rankx function showing all 1 in a calculated column

AlexPayette — Fri, 21 Oct 2022 15:15:27 GMT

It's not working, it's giving me all 1 again.

Re: Rankx function showing all 1 in a calculated column

johnt75 — Fri, 21 Oct 2022 15:41:11 GMT

Rank = var ReferenceDate = 'Table'[Shift End Date] return CALCULATE( RANKX( ALL('Table'), 'Table'[Shift End Date], ReferenceDate , DESC, Dense ), ALLEXCEPT('Table', 'Table'[Shift Start Date], 'Table'[Shift End Date] ) )

Re: Rankx function showing all 1 in a calculated column

AlexPayette — Fri, 21 Oct 2022 19:24:51 GMT

It worked, thanks !