Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 -
Many thanks in advance.
Solved! Go to 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.
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.
@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 -
I have created two measures first to check validity records (DateCheck) and other for Rank -
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)
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |