Show all values when filter not applied

[Nantha]

Hi all, I'm trying to setup a dynamic dashboard with filters following this video from AppSheet training, https://www.youtube.com/watch?v=nK-Aq_ak_T8&t=2830s . I have gotten all filters to work except the filters of YES/NO column. The expression that I'm having trouble with is as follows;

IF(ISBLANK(Dashboard Filter[Carnet Filter]), TRUE, ANY(Dashboard Filter[Carnet Filter])) = [Carnet])

This particular column is YES/NO type. The problem I'm facing is instead of showing all values when the filter is not selected, its shows all values except the item which is in the filter. And when the filter is applied, it shows only the item which is in the filter, which is correct. I need all values to show when the filter is not applied. Anyone can help please?

[TeeSee1]

You have an extraneous ")" at ANY(Dashboard Filter[Carnet Filter])).

Simply remove it to make it look like this.

IF(
 ISBLANK(Dashboard Filter[Carnet Filter]), 
 TRUE, 
 ANY(Dashboard Filter[Carnet Filter]) = [Carnet]
)

[Nantha]

I tried making the change but still not working as it should. The full expression is as follows;

AND(
IF(ISBLANK(ANY(Dashboard Filter[Crew Filter])), TRUE, IN([Name], Dashboard Filter[Crew Filter])),

IF(ISBLANK(ANY(Dashboard Filter[Equipment Filter])), TRUE, IN([Equipment ID], Dashboard Filter[Equipment Filter])),

IF(
ISBLANK(Dashboard Filter[Carnet Filter]),
TRUE,
ANY(Dashboard Filter[Carnet Filter]) = [Carnet]
),

IF(ISBLANK(ANY(Dashboard Filter[RSVP Filter])), TRUE, ANY(SELECT(Dashboard Filter[RSVP Filter], [RSVP] = "Yes")))

)

[TeeSee1]

Looks like you need to enclose Dashboard Filter[Carnet Filter] with ANY() so it looks like

IF(
ISBLANK(ANY(Dashboard Filter[Carnet Filter])),
TRUE,
ANY(Dashboard Filter[Carnet Filter]) = [Carnet]
),

[Nantha]

Yes I did try that too but same results. It's not showing the entire list when filter is not applied and shows the filtered list when the filter is applied. Been going at this for 2 days now. Just can't seem to figure this out. Thanks for your suggestions by the way

[TeeSee1]

My quick test indicates that the expression is correct.

 

What happens if you just have

IF(
ISBLANK(ANY(Dashboard Filter[Carnet Filter])),
TRUE,
ANY(Dashboard Filter[Carnet Filter]) = [Carnet]
)

in your slice filter.

Another question, just to make sure that we are on the same page.

When you say " It's not showing the entire list when filter is not applied", what exactly are being shown. Even when [Carnet Filter] is NOT applied, the other filters are still applied affecting the results.

[Nantha]

Just tested with the one filter as you suggested and its displaying results similar to the earlier expression. So for example one of the crew in the list has 17 items and 3 items marked as carnet. Without applying any filters, instead of showing all 17 items, only 14 items are being displayed. The 3 items which are marked as carnet do not show. And when i apply the carnet filter, only the 3 items show in the list which is working correctly. Its the full list that i need to display when no filters are being applied.  

[Steve]

What is the row filter expression for the Dashboard Filter slice?

[Nantha]

This is the current expression;

AND(
IF(ISBLANK(ANY(Dashboard Filter[Crew Filter])), TRUE, IN([Name], Dashboard Filter[Crew Filter])),

IF(ISBLANK(ANY(Dashboard Filter[Equipment Filter])), TRUE, IN([Equipment ID], Dashboard Filter[Equipment Filter])),

IF(ISBLANK(Dashboard Filter[Carnet Filter]), TRUE, ANY(Dashboard Filter[Carnet Filter]) = [Carnet]),

IF(ISBLANK(ANY(Dashboard Filter[RSVP Filter])), TRUE, ANY(SELECT(Dashboard Filter[RSVP Filter], [RSVP] = "Yes")))

)

[SkrOYC]

Your problem seems to be on the Select() expression.

BTW, ISBLANK(Dashboard Filter[Crew Filter]) is enough, without ANY()

[Nantha]

I have removed the particular SELECT() expression and tried without it but the results are the same.  Oh and thanks for ISBLANK() update. I was following the video where the AppSheet crew team guy was using this expression so i just lifted off and applied it.

[SkrOYC]

Can you share your schema? I have a similar setup without any problems

[Nantha]

Not sure why i'm unable to upload any images

Not sure if this is what you were asking for

[Nantha]

Sample Clip

[SkrOYC]

I can't see a thing, the image is too small

[Nantha]

Sorry about that as its my first post here and i was figuring out how to upload image.

[TeeSee1]

I think I know what is going on.

When you "apply" the filter, you are choosing "TRUE",

When you "do not apply" it, you are actually choosing and applying "FALSE". So ISNOTBLANK never evaluates to TRUE (there is a FALSE value)

So if you want to continue to use the toggle switch, your expression should be

IF(
 ANY(Dashboard Filter[Crew Filter]),
 [Carnet] = ANY(Dashboard Filter[Crew Filter]),
 TRUE
)

[Nantha]

Yes i was thinking that same thing too that the filter was being applied when its not selected but the other way round. I tried your suggestion but I'm getting getting an IF statement error,

IF function is used incorrectly:the first input should be a condition that returns true or false.

[TeeSee1 · Accepted solution]

It is a typo.

Should be [Carnet Filter] rather than [Crew Filter]

[Nantha]

Awesome! Yes its working perfectly now. Thank you so much for your time and assistance. Thanks for to the rest for helping with the troubleshoot as well. Much appreciated!

[Nantha]

The total items should be 17 when filter is not applied. Camera is one of the item that is tagged as carnet and it doesn't show in the full list before filter