Help with measures to hide data based on filter selection

[rbreneman]

Hi!

I've tried this two different ways and I would expect both to work but that is not the case. Can someone help me figure out why the IsFiltered option isn't responding the way I would expect?

I have a column chart with a card and measure behind it to display text telling the user to go select something from a slicer. I've tried two different options to determine whether or not something is filtered on the slicer. I dropped a temporary multi-row card on the page so you can see the output of the measures. Both measures are formatted as whole number. Thanks in advance for any help!

1st Measure (Calculate) is this: 

Calculate = 
VAR TotalDistricts = CALCULATE(DISTINCTCOUNT('BOCES and N/RC'[DISTRICT_SHORT_NAME]),ALL('BOCES and N/RC'[DISTRICT_SHORT_NAME]))
RETURN
IF(CALCULATE(DISTINCTCOUNT('BOCES and N/RC'[DISTRICT_SHORT_NAME]),ALLSELECTED('BOCES and N/RC'[DISTRICT_SHORT_NAME]))<TotalDistricts,1,0)

This one produces the intended result except for when the user wants to select all the districts in the slicer, it ends up hiding all of the data because the filtered count now matches the TotalDistricts count. I understand why this is happening, which leads me to try option 2 below.

2nd Measure (IsFiltered) is this:

IsFiltered = IF(ISFILTERED('BOCES and N/RC'[DISTRICT_SHORT_NAME]) = FALSE(),0,1)

This one produces the correct 0 or 1 output and would solve my issue above when the user wants to select all, but when dropping the measure into the "Filters on this visual" the visual doesn't respond. I can't understand why the visual won't filter on this like it does with option 1.

[v-yalanwu-msft · Accepted solution]

Hi, @rbreneman ;

Please try to create a new table and establish a relationship：

new = SUMMARIZE('BOCES and N/RC',[DISTRICT_SHORT_NAME])

and create a ralationship:

then create a measure:

IsFiltered = IF(ISFILTERED(new[DISTRICT_SHORT_NAME]),1,0)

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rbreneman]

Hi @v-yalanwu-msft,

That worked! Thank you, thank you, thank you!! I won't pretend to understand why it needed to be done this way (using a new table) but it works and that is all that matters!

I greatly appreciate your help!

Ryan

[v-yalanwu-msft]

Hi, @rbreneman ;

For ISFILTERED() funtion , TRUE when ColumnName is being filtered directly, or when any column of TableName is being filtered directly.

And in your 2 option (ISFLRER() measure) , your visual apply measure 'IsFiltered' into Filters on this Visual, And it's set to not 0, so no matter what your slicer chooses, your chart visual is always going to be filtered, so it's always going to return 1 in current chart visual, compared to the Card visual, it's not filtered, so it can change  1 or  0 depending on what the slicer chooses.

ISFILTERED is for any filter, including Slicer or filter. So in this case, it is more applicable to Discount instead of ISFILTERED.

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rbreneman]

Thank you so much for your reply. That makes sense. My only problem with using the calculate measure for this is how to work around if the user wants to select all in the slicer. I want the visual to behave like this:

• Do not show data when nothing is selected in slicer (working today)
• Show data when one or more items are selected in slicer (working today)
• Show data when all items are selected in slicer (not working, measure reverts to "0" and data disappears)

The pbix you created and attached shows this behavior (screenshot below). If you multi-select A, B, C, and D in the slicer, the table that is filtered by calculate disappears. I need to be able to keep the visual blank until a user chooses something in the slicer, but also need to account for if they select all in the slicer. Is this possible? Thanks!