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

    Hi, this works, hope it is what you were asking

     

    It is a measure, it is using Product[Color], a column you will have to substitute with the one on which you are doing the RANK column

     

    Test =
    VAR NumberOfValuesToShowFromBottom = 4
    VAR MaxRank =
    MAXX ( ALL ( 'Product'[Color] ), [RANK] )
    VAR MinRank = MaxRank - NumberOfValuesToShowFromBottom
    VAR CurrentRank = [RANK]
    RETURN
    IF (
        CurrentRank <=MaxRank && CurrentRank >= MinRank,
        CurrentRank
    )
     

    If this helped, please consider giving kudos and mark as a solution

    me in replies or I'll lose your thread

    Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

    Consider voting this Power BI idea

    Francesco Bergamaschi

    MBA, M.Eng, M.Econ, Professor of BI