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
        )
Amar_Kumar's avatar
Amar_Kumar
Super User
8 months ago

uj91 

Bottom 10 Keywords :=
VAR RankedValue =
RANKX (
FILTER (
ALL ( 'Table'[Keyword] ),
[KPI Measures_new] <> 0
&& NOT ISBLANK ( [KPI Measures_new] )
),
[KPI Measures_new],
'Table'[Keyword], -- tie-breaker → ensures “last 10”
ASC,
SKIP
)
RETURN
IF ( RankedValue <= 10, 1 )

How to apply (must follow exactly)

  1. Table visual:

    • Keyword

    • KPI Measures_new

  2. Visual-level filter:

    • Bottom 10 Keywords → is 1

  3. (Optional) Sort:

    • KPI Measures_new → Ascending

uj91's avatar
uj91
Helper I
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
    )
  • Amar_Kumar's avatar
    Amar_Kumar
    Super User
    8 months ago

    Can you mark it as a accepted solution, please.