• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Power BI Dashboard Custom Filtering that remains static and calculating off resulting filtered dataset.

Bali2022

New Member
Hello Chandoo.org Forum! I hope everyone is doing well!

I have a specific query concerning Power BI.

The scenario I have is I'm building a dashboard where the underlying dataset contains Customer Request information.

What I need to accomplish is as follows:

I need to create a Customer Request dashboard that shows various KPIs to the customer (e.g. length of time to complete their request, amount of $ it cost them, etc.)

I then need to be able to filter the underlying customer request data (at the Power BI dashboard UI level) through a "Customer Request # Checkbox" List method. Whereby, in the dashboard UI I need a full listing of all the customer's request #s (Req123, Req456, Req 789, etc.) from my dataset, and then I can simply check or uncheck a box next to each customer request #. And if the customer request # is checked then that data is reflected in the Power BI Dashboard and used in KPI calculations, and if unchecked the customer request # data is not included in the dashboard and KPI calculations. And I need this Customer Request Checkbox Listing to remain static until it's changed by the user. So, if today a user goes into the checkbox listing and they put a checkmark next to 30 out of 50 of the customer's request #s then when they close out of Power BI and go in tomorrow (or 2 weeks from now whatever) those same 30 of 50 customer reuqest #s are selected in the checkbox listing and included in the Power BI dashboard and KPI calculations and the other 20 customer request #s are not. I hope this makes sense. And please advise if this is not the proper forum to ask this question and kindly direct me to the correct audience. Thanks so much for your time and consideration.

Warm Regards,

Jason Hayner
Business Analyst
 

Bali2022

New Member
By default, user filter is persistent.
Announcing Persistent Filters in the Power BI Service | Microsoft Power BI Blog | Microsoft Power BI

As for calculation to only include selected item. That's standard behavior of DAX measure, unless you override it with ALL() or other filter context override function.
Thanks for the reply Chihiro!

I have a follow up to my initial query. So with Persistent Filters I can select those Customer Request #s I want to continuously reflect in the Power BI Dashboard visuals. One additional piece of functionality I'm curious if Power BI can handle is as follows:

  • I select all the Customer Request #s I want to include in my dashboard visuals. So let's say my underlying customer request data table has 50 customer request #s and in my Power BI Dashboard I only select 30 of 50 to be reflected.
  • So now when I look at the dashboard the visuals and KPIs are only based on 30 of 50 customer request # records. And this will remain the case as I open and close the Power BI service overtime thanks to Persistent Filtering.
  • Now, let's say I go into Power BI and I review the dashboard with the 30 of 50 customer requests # reflected and what if after reviewing I quickly want to see the inverse of the persistent filter (e.g. the other 20 of 50 records) reflected in the dashboard, and THEN I want to quickly see all 50 of 50 records reflected and THEN quickly flip back to my original Persistent Filter of the 30 of 50 records reflected. Something akin to a toggle switch.

Do you think this is possible?

Warm Regards - Jason
 

Bali2022

New Member
It's possible. But you will need to set up bookmark for your visual.
Overview of bookmarks in Power BI service reports - Power BI | Microsoft Learn

It won't be dynamic. Personally I prefer to keep slicer option to be less than 12 or so. I use custom grouping etc within data model and build hierarchy slicers, so it's easier to navigate through the data.
Thanks for the reply Chihiro!

Could you be so kind to give me an example of what you mean by..."I use custom grouping etc within data model and build hierarchy slicers, so it's easier to navigate through the data."
 

Chihiro

Excel Ninja
It really depends.
Ex: If I have list of cities in North America and corresponding data.
I'll add country, province/state and region/county columns to dimension table. This allows me to first filter by country, then by province etc. Each subsequent slicer is already filtered by previous. So in the end, I only have small subset to navigate through.

Alternately you can create a bucket function in PowerQuery and categorize based on value(s). And then use that to navigate. Or do the same in DAX.
See link for detail on bucketing data.
Power Query - Without using IF function | Chandoo.org Excel Forums - Become Awesome in Excel
Dynamic Banding or Grouping in Power BI – Using DAX Measures – Choose the Size of Bins - RADACAD
 
Top