DAX code to Input data

[Robyrubyjane]

hi guys I need help, so i have data like this..

and if we filter in one of data in sitename column, it'll be like this

as we can see there is 4 sitename that has same name and SiteID but each them have different sonumb, so in the column tenant rank i want it'll rank based on the oldest date, like this

if we clear the filter, it'll be like this

and if we sorted by SiteName, It'll be like this

there is 15k data in my masterlist, and it must be take lots of time if I input tenant rank one by one which is that I have to filter each siteID to see is there only 1 siteName or maybe more that actually has different sonumb. I've been looking for answer like a week and still haven't find the answer :'(

TIA

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

Tenant rank measure: =
IF (
    NOT ISBLANK ( MAX ( Data[rental start] ) ) && HASONEVALUE ( Site[siteID] ),
    RANKX (
        FILTER ( ALL ( Data ), Data[siteID] = SELECTEDVALUE ( Data[siteID] ) ),
        CALCULATE ( MAX ( Data[rental start] ) ),
        ,
        ASC
    )
)