Hiding visual when multiple slicers

YellowSquirrel — Wed, 26 Jul 2023 10:33:32 GMT

Morning all,

I'm hoping there's someone out there that can help me as my brain is melting a bit!!

I have two tabs in power bi, on one tab I manage to hide my table in the report view using the following calculation when a selection is not made on the T_code slicer:

VisualFilterAll = IF(ISFILTERED(Grouped[T_Code]),1,0)

On the second tab I have two slicers, so I have tried to do the following:

VisualPeopleHideCol = IF(OR(

ISFILTERED(Grouped[MasterEmployeeName]),ISFILTERED(tbl_Base_KeyDetails[Sub_Capability])),

)

That didn’t work so I had a look to see whether it was an issue with the Grouped[MasterEmployeeName] or tbl_Base_KeyDetails[Sub_Capability]

I created:

Test 1 = IF(ISFILTERED(tbl_Base_KeyDetails[Sub_Capability]),1,0) – this would hide the table in the visual when the sub capability slicer wasn’t selected from the slicer and show the table when the sub capability slicer was selected

Test 2 = IF(ISFILTERED(Grouped[MasterEmployeeName]),1,0) – the table remained shown whether the MasterEmployeeName Slicer was selected or not

This to note but I’m not sure whether this affects it or not:

the sub_capability and MasterEmployeeName columns are text values
There is a relationship between both tables
The MasterEmployeeName column is a calculated column made up of the following calculation to improve the data quality between the merging of the datasets:

MasterEmployeeName =

IF (

NOT ( ISBLANK ( 'Grouped'[FullName] ) ),

'Grouped'[FullName],

IF (

ISBLANK ( 'Grouped'[FullName] ) &&

NOT ( ISBLANK ( 'Grouped'[MasterEmployeeNumber] ) ) &&

NOT ( ISBLANK ( 'Grouped'[Resource Name] ) ),

VAR MasterEmployeeNumber =

CALCULATE(

MAX ( 'Grouped'[MasterEmployeeNumber] ),

ALLEXCEPT( 'Grouped', 'Grouped'[Resource Name] )

)

RETURN

IF (

'Grouped'[MasterEmployeeNumber] = MasterEmployeeNumber,

'Grouped'[Resource Name],

BLANK()

BLANK ()

)

Re: Hiding visual when multiple slicers

technolog — Sun, 03 Sep 2023 17:47:39 GMT

Firstly, your formula for hiding the table based on the T_Code slicer seems to be working just fine. That's a good start.

Now, for the second tab where you have two slicers, you're trying to hide the table based on whether either of the slicers (MasterEmployeeName or Sub_Capability) is selected or not.

Your individual tests for each slicer seem to indicate that the Sub_Capability slicer is working as expected, but the MasterEmployeeName slicer isn't.

Considering that MasterEmployeeName is a calculated column, there's a possibility that the issue might be related to the way the column is calculated. However, before diving into that, let's first ensure that the logic for hiding/showing the table is correct.

Your formula for VisualPeopleHideCol is:

VisualPeopleHideCol = IF(OR(
ISFILTERED(Grouped[MasterEmployeeName]),ISFILTERED(tbl_Base_KeyDetails[Sub_Capability])),
1,
0
)
This formula checks if either of the slicers is filtered. If either one is filtered, it returns 1, otherwise 0. This seems correct based on your requirement.

Given that Test 2 doesn't work as expected, let's focus on the MasterEmployeeName slicer. The fact that it's a calculated column might be the root cause of the issue.

The calculation for MasterEmployeeName is a bit complex. It checks if FullName is not blank, then it uses that. If FullName is blank, it checks other conditions and uses Resource Name if certain conditions are met.

One thing to note is that slicers can sometimes behave unexpectedly with calculated columns, especially if the calculation involves other columns from the same table or other tables.

Here's a suggestion: Instead of using the calculated column directly in the slicer, try creating a separate table that contains the unique values of MasterEmployeeName and use that table for the slicer. This way, you're not directly filtering on the calculated column, but on a static list of values derived from it.

To create this table, you can use a DAX formula like:

MasterEmployeeNames = VALUES(Grouped[MasterEmployeeName])
Then, use this new table for your slicer. Ensure that there's a relationship between this new table and your original Grouped table based on MasterEmployeeName.

Once you've done this, test again with your VisualPeopleHideCol formula to see if the table hides/shows correctly based on the slicer selections.

topic Hiding visual when multiple slicers in DAX Commands and Tips

Hiding visual when multiple slicers

Re: Hiding visual when multiple slicers