Include and Exclude Button

Hi there!

So, I’ve been able to do this to the backend in Power Query as suggested in a previous discussion.

However, in my current scenario, the idea is for the front-end user to also have the ability to exclude certain report rows from the report. This can easily be done using the Filter Pane for all pages (see attached screenshots)? This option is however not something the user wants to do. The user wants to simply click a button or similar and achieve the same thing.

So, in summary, the front-end user must have the ability to toggle between the two data states with a click of a button. How do I achieve this without having to add extra DAX code to each Measure, assuming this has to be applied to many related tables that have many Measures?

Project_Exclude.zip (474.2 KB)

You may try this out
in the data model: Include a column from example with values as include, exclude
Create two reports : one with all the data, one without ‘project-exclude’
create two bookmark button
Create Bookmark - ON
Create Bookmark - OFF
enable things as per the attached screenshot
place the reports one over other
based on toggle selection, the report will appear

screenshot.docx (31.4 KB)

I have managed this scenario with an extra column in a dimension table.
My scenario was toggling between vehicle sales and servicing in a motor trade environment and I only needed to do it in one table.

Reckon this is doable for you but may need a bit more M on your tables to make it happen.

Pete

Hi @BINavPete! I was wondering if your idea is similar to @UmaSuresh’s or is it different? Are you able to expand on that a bit and would the toggle button be accessible to the user in the Power BI Service?

Thanks!

Hi @UmaSuresh!

This sounds interesting, I will give it a go. So, once you click either mode, does the report maintain the state even while the user is navigating and filtering the report?

Thanks!

Dear @powerbideveloper

By using bookmark, we shall switch between reports. I used this for showing chart view and table view as I don;t want to show both in the same page.

screenshot_exclude_include_toggle_bookmark.docx (47.9 KB)
I am attaching a screenshot for your review

Regards
S Uma

HI guys
I think @UmaSuresh solution here might be easier to put in play, especially if you what you have in mind impacts a lot of dimension tables. My approach would make the DAX a bit messy probably with far too much TREATAS.

But if it is only one dimension table let me know and I’ll come back in on the post

Pete

HI @BINavPete and @UmaSuresh!

So, here’s what I did. I took Uma Suresh’s idea of using bookmarks for this, and used buttons to toggle between the two states. The one difference is that I am using the Filter Pane to filter the items with suffixes. When I toggle between the two states, the Card metric calculations and other chart visuals change accordingly.

To see if this filters correctly throughout I will probably need to check each user case scenario. I welcome any comments and suggestions about potential pitfalls with this solution.

Bumping this post for more visibility.