Forum Discussion
RANKX with condition
Hi,
I am trying to create a RANKX measure on customers meeting a certain revenue conditions.
Rank top 100 =
VAR Customer_list =
FILTER(
ALLSELECTED(Revenue[Customer]),
[Revenue total] < 50000000 && [Revenue total] > 30000000
)
VAR RANK_DESC =
RANKX(
FILTER(ALLSELECTED(Revenue[Customer]), Revenue[Customer] IN Customer_list),
[Revenue total],
,
DESC)
RETURN
IF (ISINSCOPE(Revenue[Customer]),
IF(RANK_DESC <= 100, RANK_DESC, BLANK()),
BLANK()
)
However the measure would still evaluate customers which do not meet the criteria.
The dataset is a standard sales transactional table: date, customer, revenue.
Any ideas would be appreciated.
Best regards.
Maikeru , Prefer filter in measure and then use it RankX. Means have all filter in the measure and then use that measure in Rank
For Rank Refer these links
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-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
5 Replies
Maikeru , Prefer filter in measure and then use it RankX. Means have all filter in the measure and then use that measure in Rank
For Rank Refer these links
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-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finaleThanks amitchandak., for the articles.
The trick to use CONCATENEX is very useful. Now I understand better the inner mechanics of RANKX.I finally went for the RANKX applied on a filtered measure solution.
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Hi Maikeru ,
In your following DAX, can you use your "Customer_list" directly, rather using IN operator.
Basically, rank your customer List and check. This should resolve the isssue.
VAR RANK_DESC = RANKX( FILTER(ALLSELECTED(Revenue[Customer]), Revenue[Customer] IN Customer_list), [Revenue total], , DESC)
So, in above RANKX directly use CUSTOMER LIST.
If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂
Thanks,
Pragati
