Forum Discussion
Dynamic RANK on contract and monthly level not working
Hello Everyone,
I'm quite new to PoweBI. I've been reading posts here and trying many things to get a rank to work which I could get done in the back end with SQL rank and partition but I need to implement it in the dashboard to make it dynamic, depending on user selections.
So I have a set of lease contracts that have been restructured/modified due to COVID and other reasons since 2018. I need to rank the restructuring event(s) per contract depending on the date (year and month) when they were restructured. So I need a RANK on contract level that tells me which restructure happened first, second etc depending on the ModifiactionDate slicer selection. Then taking the max of the rank I can count how many times each contract has been modified and calculate other KPIs I need like First wave mods (RANK = 1) and Second wave mods (RANK >=2). There are 2 restructure statuses: Booked (IsFinished i= 1 in the data model) and Pipeline(IsPipeline = 1) which the user can select through a slicer (Booked/Pipeline), and the rank needs to have the selection in consideration plus the ModificationDate Slicer (DAte between type).
This is what I should get if I don't filter out the ModifDate or Modification Status (SQL ranks on all history):
All the necessary fields are within the same table: R_TEST and they are:
| CONTRACT_ID | ModifDate | ModifStatus | Is Restructured | IsPipeline | IsFinished |
This is one of the DAX I tried that throws one for everything:
And here is how I made the ranks work in SQL:
,RANK() OVER (PARTITION BY Country, CONTRACT_ID ORDER BY Country, PartnerId, CONTRACT_ID, IsFinished DESC, ModifDate ) RNK_CONTRACT_ALL_MOD_HIST --For Booked and Pipeline Restructures
,(CASE WHEN IsFinished = 1
THEN RANK() OVER (PARTITION BY Country, CONTRACT_ID ORDER BY Country, PartnerId, CONTRACT_ID, IsFinished DESC, ModifDate )
END ) RNK_CONTRACT_BOOK_MOD_HIST --For Booked Restructures
BTW I already removed duplicates (restructures on the same contract during the same month) so that the ranks are unique
Here is a sample of the data set, help would be much appreciated!!!!!!!
| COUNTRY_CODE | CONTRACT_ID | PartnerId | ModifDate | ModifReason | ModifStatus | Is Restructured | IsPipeline | IsFinished | RNK_CONTRACT_ALL | RNK_CONTRACT_ALL_MOD_HIST | RNK_CONTRACT_BOOK_MOD_HIST | RNK_CONTRACT_PIP_MOD_HIST | RNK_CONTRACT_MOD_YR_ALL | RNK_CONTRACT_BOOK_MOD_YR | RNK_CONTRACT_PIP_MOD_YR |
| AT | AT_95219_8AT13393371F | CHARISMAAT-135662 | 4/6/2020 | COVID-19 | Booked Mods | 1 | 0 | 1 | 1 | 1 | 1 | NULL | 1 | 1 | NULL |
| AT | AT_95219_8AT13393371F | CHARISMAAT-135662 | 11/4/2020 | COVID-19 | Pipeline Mods | 1 | 1 | 0 | 2 | 2 | NULL | 1 | 2 | NULL | 1 |
| DE | DE_1_227482 | CHARISMADE-144277 | 3/26/2020 | Other | Booked Mods | 1 | 0 | 1 | 1 | 1 | 1 | NULL | 1 | 1 | NULL |
| DE | DE_1_227482 | CHARISMADE-144277 | 6/24/2020 | Other | Booked Mods | 1 | 0 | 1 | 2 | 2 | 2 | NULL | 2 | 2 | NULL |
| DE | DE_1_227482 | CHARISMADE-144277 | 8/12/2020 | Other | Booked Mods | 1 | 0 | 1 | 3 | 3 | 3 | NULL | 3 | 3 | NULL |
| DE | DE_1_227482 | CHARISMADE-144277 | 12/8/2020 | Other | Pipeline Mods | 1 | 1 | 0 | 4 | 4 | NULL | 1 | 4 | NULL | 1 |
| DE | DE_1_227485 | CHARISMADE-144277 | 3/26/2020 | Other | Booked Mods | 1 | 0 | 1 | 1 | 1 | 1 | NULL | 1 | 1 | NULL |
| DE | DE_1_227485 | CHARISMADE-144277 | 6/24/2020 | Other | Booked Mods | 1 | 0 | 1 | 2 | 2 | 2 | NULL | 2 | 2 | NULL |
| DE | DE_1_227485 | CHARISMADE-144277 | 8/12/2020 | Other | Booked Mods | 1 | 0 | 1 | 3 | 3 | 3 | NULL | 3 | 3 | NULL |
| DE | DE_1_227485 | CHARISMADE-144277 | 12/8/2020 | Other | Pipeline Mods | 1 | 1 | 0 | 4 | 4 | NULL | 1 | 4 | NULL | 1 |
| DE | DE_1_234766 | CHARISMADE-162546 | 3/23/2020 | Other | Booked Mods | 1 | 0 | 1 | 1 | 1 | 1 | NULL | 1 | 1 | NULL |
| DE | DE_1_234766 | CHARISMADE-162546 | 9/1/2020 | COVID-19 | Booked Mods | 1 | 0 | 1 | 2 | 2 | 2 | NULL | 2 | 2 | NULL |
| DE | DE_1_234766 | CHARISMADE-162546 | 12/3/2020 | Other | Booked Mods | 1 | 0 | 1 | 3 | 3 | 3 | NULL | 3 | 3 | NULL |
| AT | AT_95219_AT2064097-1 | CHARISMAAT-141009 | 1/15/2019 | Other | Booked Mods | 1 | 0 | 1 | 1 | 1 | 1 | NULL | 1 | 1 | NULL |
| AT | AT_95219_AT2064097-1 | CHARISMAAT-141009 | 3/4/2020 | Other | Booked Mods | 1 | 0 | 1 | 2 | 2 | 2 | NULL | 1 | 1 | NULL |
| AT | AT_95219_AT2064097-1 | CHARISMAAT-141009 | 11/6/2020 | COVID-19 | Booked Mods | 1 | 0 | 1 | 3 | 3 | 3 | NULL | 2 | 2 | NULL |
2 Replies
Anonymous , Do you have any column in visual that do not below to table R_TEST and not aggregated , if so rank will inside that column . Also, add calculate on distinctcount and check
PBI RANK =
RANKX (
FILTER ( ALL ( R_TEST )
,R_TEST[IsFinished] = 1 && R_TEST[IsFinished] = 1
)
,calculate(DISTINCTCOUNT(R_TEST[CONTRACT_ID])) --R_TEST[OVERALL_SORT]
,--1/0
,ASC
,Skip --Dense
)For Rank Refer these links, if Needed
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finaleamitchandak Yes all fields are in the same table, including the rank measure. I tried DISTINCTCOUNT but it didn't work. I'm not sure using DISTINCTCOUNT on CONTRACT_ID is what I need as I need to rank the CONTRACt IDs by ModifDate, not the unique values!
