Forum Discussion

Anonymous

Not applicable

5 years ago

Solved

RANKX on filtered table

Apologies if this is obvious, i have not been able to find a solution for this.

I have this measure

RANKX(ALLSELECTED(Table1),CALCULATE([measure), , DESC)

I would like to filter Table 1 so that only certain rows are ranked. I can achieve the same thing with a visual level filter but just looking to see if there is a simple way to do it in the measure.

Worth noting that [measure] is a sum of a seperate related table

=
IF(
    MAX( table1[PUBLISHED] ),
    RANKX(
        CALCULATETABLE( ALLSELECTED( table1[some column] ), table1[PUBLISHED] = TRUE ),
        [measure]
    )
)

4 Replies

CNENFRNL
Community Champion
5 years ago

= IF( MAX( table1[PUBLISHED] ), RANKX( CALCULATETABLE( ALLSELECTED( table1[some column] ), table1[PUBLISHED] = TRUE ), [measure] ) )
- Anonymous
  Not applicable
  5 years ago
  Brilliant, this works perfectly, thanks for your help.
selimovd
Most Valuable Professional
5 years ago
Hey Anonymous ,

you can use the FILTER function to filter something.
And if you use a measure you don't need a CALCULATE without any parameters. Try the following approach:
RANKX(FILTER(ALLSELECTED(Table1), Table1[myColumn] = "MyValue"),[measure], , DESC)

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic
- Anonymous
  Not applicable
  5 years ago
  Hi Denis,
  
  I have tried this but it is still giving ranks to rows that do not have that value:
  
  RANKX(FILTER(ALLSELECTED(table1),table1[PUBLISHED]=true),[measure] , ,DESC)

Related content