Set analysis only show table when certain filters are active

[Maggie_1]

I have a master measure which I'm showing in a table and a kpi. However, there are only 5 filters with which it is accurate. If any other filter is selected, the measure is inaccurate. I would therefore like to hide the kpi and table when any filter other than Filter 1-5 is selected. Adittionally, the measure is also accurate if no filter is selected.

I thought I could do this with a layout box and let something appear in front of it if anything other than Filter 1-5 is selected, but I can't figure out the set analysis I would need for it. Checking if all other filters are not selected is not an option since we have over 20 filters. If anyone knows which formula to use or has another suggestion I would really appreciate it!

[james598henry]

---

@Maggie_1 wrote:

I have a master measure which I'm showing in a table and a kpi. However, there are only 5 filters with which it is accurate. If any other filter is selected, the measure is inaccurate. I would therefore like to hide the kpi and table when any filter other than Filter 1-5 is selected. Adittionally, the measure is also accurate if no filter is selected.

I thought I could do this with a layout box and let something appear in front of it if anything other than Filter 1-5 is selected, but I can't figure out the set analysis I would need for it. Checking if all other filters are not selected is not an option since we have over 20 filters. If anyone knows which formula to use or has another suggestion I would really appreciate it!

---

Hello @Maggie_1 floridablue ,

You can use a set analysis formula to achieve this. Here's a suggestion:

Create a new measure to determine if the selected filter is one of the valid filters (Filter 1-5).

Use this measure to control the visibility of your KPI and table.

Here's a sample formula:

=if(Count({1<Filter= {'Filter1', 'Filter2', 'Filter3', 'Filter4', 'Filter5'}>} Filter) > 0, 1, 0)

This formula checks if the selected filter is one of the valid filters and returns 1 if true, otherwise 0. You can then use this measure to control the visibility of your KPI and table.

Best Regards,
James Henry

[AnsweringTuring · Accepted solution]

To hide the KPI and table when filters other than Filter 1-5 are selected, you can use a calculation condition based on the GetSelectedCount() function. This function returns the number of values selected in a given field.

First, create a new calculated field that checks if any of the filters from Filter 1-5 are selected or if no filter is selected. The formula could be:

=GetSelectedCount([Filter 1]) + GetSelectedCount([Filter 2]) + GetSelectedCount([Filter 3]) + GetSelectedCount([Filter 4]) + GetSelectedCount([Filter 5]) > 0 OR GetPossibleCount([Filter 1]) = 1

This expression evaluates to True if any of the filters from Filter 1-5 have a selection or if no filter is selected (GetPossibleCount([Filter 1]) = 1 means there is only one possible value for Filter 1, i.e., no selection).

Then, in the properties panel of the KPI and table, under Appearance > Conditional, add a new condition based on the calculated field you created. Set it to show the object when the condition is True and hide it when False.

This way, the KPI and table will only be visible when the filters from Filter 1-5 are selected or when no filter is applied, effectively hiding them when any other filter is used.

[Maggie_1]

Thanks, this worked for me. Your explanation is also very clear, thank you!

The one thing that was different was that GetPossibleCount gave the number of options that were available when I didn't have anything selected. (In my case I had 3 unique values in that column, so I had to do (GetPossibleCount([Filter 1]) = 3)

[Maggie_1]

Hey @james598henry ,

This sadly doesn't work for me and gives an error. I think it's because "Filter" is undefined, so it does not know what to count. Do you know what I could use instead? Just to clarify which filter I mean:

=if(Count({1<Filter= {'Filter1', 'Filter2', 'Filter3', 'Filter4', 'Filter5'}>} Filter) > 0, 1, 0)

[seanbruton]

Kudos nice solution!!

[seanbruton]

Kudos nice simple solution!!