Forum Discussion

uj91's avatar
uj91
Helper I
8 months ago
Solved

Bottom 10 considering repeated value.

So i need bottom 10 keywords  in a table considering repeated value meaning just last 10 entries of keyword. ALso i dot want to show value 0.  For example in the above table there are im...
Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • uj91's avatar
    uj91
    8 months ago
    I tweaked your fomrula and it worked !

    Bottom     10 Keywords =
    VAR BaseTable =
        FILTER (
            ALL ( 'Reporting.Search'[KeywordText] ),
            [KPI Measures_new] <> 0
                && NOT ISBLANK ( [KPI Measures_new] )
        )

    VAR Bottom10Table =
        TOPN (
            10,
            BaseTable,
            [KPI Measures_new], ASC,
            'Reporting.Search'[KeywordText], ASC   -- deterministic tie-breaker
        )

    RETURN
        IF (
            CONTAINS (
                Bottom10Table,
                'Reporting.Search'[KeywordText],
                SELECTEDVALUE ( 'Reporting.Search'[KeywordText] )
            ),
            1
        )
cengizhanarslan's avatar
cengizhanarslan
Super User
8 months ago
Rank_Bottom10 =
VAR Rank_ =
RANKX ( FILTER ( ALLSELECTED ( DimKeyword[Keyword] ), [KPI Measures_new] > 0 ), [KPI Measures_new], , ASC, DENSE)
RETURN
IF ( Rank_ <= 10, Rank_ )
 
Use the formula above, and then use it in your visual as a measure filter by filtering only non-blank values.
  • uj91's avatar
    uj91
    Helper I
    8 months ago

    not working as expected. I need to show only 10 bottom entries with least value (values repeated are fine). 

    • cengizhanarslan's avatar
      cengizhanarslan
      Super User
      8 months ago

      Blank values brokes the formula. Use below, I tested it, works fine.

       

      Rank_Bottom10 =
      VAR Rank_ =
      RANKX ( FILTER ( ALLSELECTED ( DimKeyword[Keyword] ), [KPI Measures_new] > 0 ), [KPI Measures_new], , ASC, DENSE)
      RETURN

      IF ( Rank_ <= 10 && [KPI Measures_new] > 0, Rank_ )
       
      Use the formula above, and then use it in your visual as a measure filter by filtering only non-blank values.