Dynamic RANK on contract and monthly level not working

[Anonymous]

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:

PBI RANK =

RANKX (

FILTER ( ALL ( R_TEST )

,R_TEST[IsFinished] = 1 && R_TEST[IsFinished] = 1

)

,DISTINCTCOUNT(R_TEST[CONTRACT_ID]) --R_TEST[OVERALL_SORT]

,--1/0

,ASC

,Skip --Dense

)

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

[amitchandak]

@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-finale

[Anonymous]

@amitchandak 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!