Rankx with a measure filter

[Chifflee]

Hello Guys,

I would like to ask your help:

Here the formula: TOP10_max =RANKX(ALL('product'[itemno]),[Overall scrap%],,DESC)
I used this measure to show the top10 item numbers (as a visualization filter - less than 11) by the highest scrap%. Which is worked properly.

But, now... I have to use a filter where the overall produced parts are > 10000 and I don't know how could I implement it. Because I have a measure for overall parts, but if I use as a visualization filter, it will filter only these 10 items where the produced parts are higher than 10000. So, is it possible to use a measure inside the rankx?
Produced parts are located in an another table where you can find the item number also, 'product'[itemno] is a main table without duplications.

Any idea?

[v-yiruan-msft · Accepted solution]

Hi @Chifflee ,

Do you want to get the rank which overall product parts greater than 10000 and Overall scrap% is top 10? If yes, you can create two measures as below:

Rank =
IF (
    [Overall products] >= 10000,
    RANKX ( ALLSELECTED ( 'product' ), CALCULATE ( [Overall scrap%] ) )
)

Top 10 =
VAR _nrank =
    IF (
        ISBLANK ( [Rank] ),
        BLANK (),
        RANKX ( ALLSELECTED ( 'product' ), [Rank],, ASC, DENSE ) - 1
    )
RETURN
    IF ( _nrank <= 10, _nrank, BLANK () )

Otherwise, please provide some sample data in your model (exclude sensitive data) and your expected result with examples. Thank you.

Best Regards
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.

[Chifflee]

Hi @v-yiruan-msft ,

Ah, I think your solution is works properly if I use visual level filters on the measures (not blank). 🙂 

Thanks a lot!

[amitchandak]

@Chifflee , on the way, use visual level filter for the part producted > 10000

TOP10_max =RANKX(ALLSELECTED('product'[itemno]),[Overall scrap%],,DESC)

or try like

TOP10_max =RANKX(ALLSELECTED('product'[itemno]),calculate([Overall scrap%], filter(Values('product'[itemno]),[part producted] >10000)),,DESC)

[Chifflee]

Hello @amitchandak ,

Your first suggestion: same result with visual level filter.

Second: Every item numbers has 1st rank, so top10 filter doesn't works. 😞