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
        )
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.