Filter visuals with multiple checkboxes

[bhalicki]

Hi all,

Hoping someone has a better solution.

I have a client who would like to filter a table based on checkboxes.  Each checkbox may have different criteria.  Example below:

In the past, I have set this up using a measure which returns the total number of projects which meet the criteria (using COUNTROWS).  I then apply the measure to the table as a filter and filter everything where the measure returns one or more.

Project:

Expense:

FilterTable (disconnected table):

Filter Measure:

FilterMeasure =

SWITCH(TRUE(),

       /* No options selected, so show all options */

       NOT ISFILTERED(FilterTable), 1,

       /* both options must be selected, show:

            - Only projects with expenses

            - Projects with expenses >$2500

       */

       ISFILTERED(FilterTable) && SELECTEDVALUE(FilterTable[Column1])=BLANK(),

       CALCULATE(COUNTROWS(Project), Expense[ExpenseTotal] <> 0, Expense[ExpenseTotal] > 2500),

       /* Only "show projects with expenses" selected */

       ISFILTERED(FilterTable) && SELECTEDVALUE(FilterTable[Column1])="Show only projects with expenses",

       CALCULATE(COUNTROWS(Project), Expense[ExpenseTotal] <> 0),

       /* Only "show projects with expenses greater than $2500" selected */

       ISFILTERED(FilterTable) && SELECTEDVALUE(FilterTable[Column1])="Show projects with expenses greater than $2500",

       CALCULATE(COUNTROWS(Project), Expense[ExpenseTotal] > 2500))

Trouble is, I now have requirements to add more and more options to the filter list.  The amount of data to be filtered is also quite large, plus one of the pages has more than 10 card visuals, which all need to have the same filter logic applied.  So while this solution worked initially, it's not really scalable and hoping someone has a better solution.

Kind regards,

Ben.

[bhalicki]

Hi @Jihwan_Kim , thanks for your reply, very much appreciated.

Calculation groups might be a nice solution, but from what I understand there isn't a way to handle multiple calculation items selected simultaneously?  I can see this discussed, but haven't been able to find a way to implement this in PowerBi?

Kind regards,

Ben.

[v-achippa · Accepted solution]

Hi @bhalicki,

Thank you for reaching out to Microsoft Fabric Community.

Power BI cannot use two or more calculation items from the same calculation group at the same time.

Calculation groups are single select slicer, so they cannot behave like a checklist so every new selection overwrites the previous one. For a true multi select filter you will need a regular slicer that lists every rule.

So se the disconnected table with boolean flag for each rule and use a measure that checks whether any of the flags chosen in the slicer is TRUE for the current row.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @bhalicki,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution I have provided for the issue worked? or let us know if you need any further assistance.
If my response addressed, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @bhalicki,

We wanted to kindly follow up to check if the solution I have provided for the issue worked.
If my response addressed, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @bhalicki,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution I have provided for the issue worked.
If my response addressed, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but try creating one Calculation Group and one Calculation Item to apply it to the page level (or all), instead of creating a filter measure and applying it measure by measure.

Create calculation groups in Power BI - Power BI | Microsoft Learn