Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Blue_BIGuy
Frequent Visitor

Rank customer over Date table for various slicer

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). 

Blue_BIGuy_0-1642503921535.png

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 - 

Blue_BIGuy_0-1642504359277.png

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. 

 

1 ACCEPTED SOLUTION
v-angzheng-msft
Community Support
Community Support

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.

View solution in original post

4 REPLIES 4
v-angzheng-msft
Community Support
Community Support

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
Super User
Super User

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

Hey Amit, 

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

Blue_BIGuy_0-1642577282763.png

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 ! 
 

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) 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.