RANKX with condition

[Maikeru]

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.

[amitchandak · Accepted solution]

@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

[Maikeru]

Thanks @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.

[Pragati11]

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

[Maikeru]

Thanks @Pragati11.

I tried your idea, but the result was exactly the same.

    RANKX(
        FILTER(
            ALLSELECTED(Revenue[Customer]), 
            [Revenue total] < 50000000 && [Revenue total] > 30000000
            ),
        [Revenue total],
        ,
        DESC)

[Greg_Deckler]

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