Rank customer over Date table for various slicer

[Blue_BIGuy]

Hello Experts, 

I am stuck with this issue and would like your guidance. 

I need to rank customer for selected PropertyName and Date (Date table, independent i.e. no relationship is maintain). 

There is a filter applied on the table which first check valid customer for selected date (calculation mention below), and then for these filtered customer I need calculate customer with top 3 Annual rent for selected PropertyName slicer.

I have two table Date and Detail - 

And Validity Check measure is derived as below - 

 IF(HASONEVALUE('Calendar'[Date]),IF(MAX(Details[RentStart])<=MIN('Calendar'[Date])&&MAX(Details[RentEnd])>=MAX('Calendar'[Date]),1,0),1)

Many thanks in advance. 

[v-angzheng-msft · Accepted solution]

Hi, @Blue_BIGuy 

Try this:

Rank =
RANKX(
    FILTER(
        ADDCOLUMNS( ALL( 'custom' ), "Validity Check", [DateCheck] ),
        [Validity Check] = 1
    ),
    'custom'[AnnualRent(M)],
    ,
    DESC,
    DENSE
)

If this doesn't work for you, please consider sharing a sample file without any sesentive information here, then I can try to look into it to come up with a more accurate measure.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Blue_BIGuy , If you have measure [Annual Rent] then you can top 3 customer like

calculate([Annual Rent],TOPN(3,allselected(table[customer]),[Annual Rent],asc), values(table[customer]))

How you would like date to play role ?

[Blue_BIGuy]

Hey Amit, 

If you need further details, so here the screen shot from my actual dataset, this might explain - 

I have created two measures first to check validity records (DateCheck) and other for Rank -

DateCheck =

IF(HASONEVALUE('Date'[Date]),IF(MAX('custom'[RentStart])<=MIN('Date'[Date])&&MAX('custom'[RentEnd])>=MAX('Date'[Date]),1,0),0)

Rank = RANKX(ALL('custom'[TenantName],'custom'[TradeSector], 'custom'[LeaseExpiryDate], 'custom '[RentStart],'custom'[RentEnd]), 'custom'[AnnualRent(M)], , DESC, Dense)

From screen shot you see if I select date as 31.12.2021 then there are only 2 records valid and I want to display top 10 valid tenant i.e. Top 10 tenant with Date Check field values as 1.

Thanks ! 

[Blue_BIGuy]

Thanks Amit, 

But the formual that you have provided is not working. 

My requirement is, to let user select any date and Property Name from slicer. For selected property name and date show top 10 valid customer (validity check is done based on this logic - rentStart <= Selected date and rentEnd >= Selected date)