Hi,
I am really struggling with WINDOW function in DAX. Clearly I do not fully understand how it works, if I am getting this result.
I am trying to create a rank column, by dividing the data set per "groups". I know it can be done with RANKX, but when I discovered WINDOW function I though "Oh, I can just give it a go, to test my udnerstanding of it.", which clearly failed...
In my dataset, I have a reference numbers, and each of there references have been placed "on hold", sometimes more than once.
I am trying to create a rank column, which when there is more than one on hold instance, is going to count the reference based on the On hold Start Date.
I am trying to create the "RANK COLUMN", by using RANKX + WINDOW function.
| Reference | Start Date | End Date | RANK COLUMN |
| 100963 | 07/02/2022 | 10/05/2022 | 1 |
| 100963 | 20/07/2022 | 17/08/2022 | 2 |
| 100963 | 25/07/2022 | 27/07/2022 | 3 |
| 100964 | 25/07/2022 | 27/07/2022 | 1 |
| 100965 | 27/01/2023 | 03/03/2023 | 3 |
| 100965 | 09/06/2022 | 20/01/2023 | 1 |
| 100965 | 25/07/2022 | 27/07/2022 | 2 |
What I have so far:
RANKX TEST =
RANKX(
WINDOW(1, ABS, 1, ABS,
,
ORDERBY('Ref on hold -Copy'[Start Date], ASC),,
PARTITIONBY('Ref on hold -Copy'[Reference])),
'Ref on hold -Copy'[Reference],,ASC,Dense)
This calculated column returns:
| Reference | Start Date | End Date | RANK COLUMN |
| 100963 | 07/02/2022 | 10/05/2022 | 1 |
| 100963 | 20/07/2022 | 17/08/2022 | 1 |
| 100963 | 25/07/2022 | 27/07/2022 | 1 |
| 100964 | 25/07/2022 | 27/07/2022 | 1 |
| 100965 | 27/01/2023 | 03/03/2023 | 1 |
| 100965 | 09/06/2022 | 20/01/2023 | 1 |
| 100965 | 25/07/2022 | 27/07/2022 | 1 |
I know I have ommitted the "Relation" parameter, but whatever I have tried in there I get this error:
"WINDOW's Relation parameter may have duplicate rows. This is not allowed."
I have tried fixing it witl ALL('Table name')), or ALLSELECTED(), or other table funcitons, but getting the same error.
I tried DISTINCT(ALL('table Name'), but then got a circular dependacy error.
Surely, I am doing something wrong. To create a row count/rank, you need to partition by your dataset and order by a column, but I just got in a relm of error messages.
PLEASE HELP!
Thank you!
