Forum Discussion
Rankx function showing all 1 in a calculated column
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 |
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] ) )
4 Replies
- amitchandak
Super User
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)It's not working, it's giving me all 1 again.
johnt75Rank = 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] ) )It worked, thanks !
