PowerBI failing to maintain Bookmarks

[Anonymous]

Hi there,

I have three buttons:

"All"

"Top 3"

"Bottom 3"

These are bookmarks that will change the visuals to a version that has some filtering applied (i.e top N based on Country by Values field).

This works perfectly.. I thought.

Randomly PowerBI removes those filters, causing inconsistency and unhappy clients. If anyone has any ideas please let me know. I have one idea, but I am now sure what the expression would look like in DAX for Top 3 or Bottom 3.

Any alternatives?,

Maybe a dynamic filter where I create a dummy table saying Top 3, Bottom 3, All.

Then creating a measure saying

IF(ISFILTERED(DummyTable),

Switch(SelectedValue(DummyTable), 

"All",

Value,

Top 3,

An equation to filter top 3 etc..

Everything in the above equation will work, however I need to know how to display top 3 values or bottom 3 values.

I have a country field, and amounts field. Therefore, display top 3 countries (legend of the stacked bar chart), based on the amounts.

Thanks,

[v-xicai · Accepted solution]

Hi @Anonymous ,

There is a solution you may considerate.

Firstly, you may create measures like DAX below.

Rank_Desc= RANKX(ALLSELECTED(Table1),Table1[amounts],,DESC ,Dense)
 
Filter_Top3=IF([Rank_Desc]<=3, 1, BLANK())
 
Rank_ASC= RANKX(ALLSELECTED(Table1),Table1[amounts],,ASC ,Dense)
 
Filter_Bottom3=IF([Rank_ASC]<=3, 1, BLANK())

Secondly, put the field [Country] into table visual. Assuming you have created "Top 3" Bookmark, then you can add the measure Filter_Top3 into the Visual level filter of table visual for "Top 3" Bookmark, setting Filter_Top3 as "is not blank". Do the same with the Filter_Bottom3 for "Bottom 3" Bookmark.

You can refer to the link Dynamic Visuals using Buttons to set Bookmarks and add Conditional Format for the buttons.

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.