Forum Discussion
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...
- 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)RETURNIF (CONTAINS (Bottom10Table,'Reporting.Search'[KeywordText],SELECTEDVALUE ( 'Reporting.Search'[KeywordText] )),1)
Rank_Bottom10 =
VAR Rank_ =
RANKX ( FILTER ( ALLSELECTED ( DimKeyword[Keyword] ), [KPI Measures_new] > 0 ), [KPI Measures_new], , ASC, DENSE)
RETURN
IF ( Rank_ <= 10, Rank_ )
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.
not working as expected. I need to show only 10 bottom entries with least value (values repeated are fine).
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)
RETURNIF ( 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.