Filtering by nulls across multiple sheets and datasets

[KonradAntczak]

Hello everyone!

I have an issue with filtering by nulls across multiple sheets. In my analysis I have two datasets: set A and set B.
Set A looks like this:

name	profession	profession_with_nulls
Anne	teacher	teacher
Marie		null

Set B looks like this:

name1	job	with_nulls
John		null
Udo	Quicksight expert	Quicksight expert

I created two sheets in my analysis. In the first sheet I have a table visualizing dataset A, and in the second one I have two tables - one visualizing dataset A and the second one visualizing dataset B. I made a parameter jobParam which is a multivalue string parameter with the default value All. I created a control based on this parameter which takes values from a set A column “profession_with_nulls”. I enabled a cross-sheet filter on the visual in the first sheet which applies to both tables visualizing dataset A. It is a parameter filter on “profession_with_nulls” column that filters only values equal the parameter jobParam.

I also made a filter for the visual based on a set B which is a parameter filter on “with_nulls” column that filters only values equal the parameter jobParam.

At the default state when All is selected in the control everything is okay, I see everything in all of the tables. But when I deselect all and select only NULL in the control, the first sheet refreshes, but the second one does not - it stays like it was.

If I select “teacher” instead of NULL, there is no problem - I see only matching record in one table and No data in the second one, which is correct.

I appreciate all the help!

[ali]

did you tried ''Apply cross dataset" option?

[ali]

Also check both dataset columns if it is actually null or not by calculated filed. i.e
ifelse(isNull(profession_with_nulls), 1 , 0)

[helseslo]

@ali

ifelse(isNull(profession_with_nulls), 1 , 0)

Hello! I tried this and, unfortunately, it didn’t work. The bug is still here.

When I select only “NULL” from the filter, the first sheet updates but the second one doesn’t.

[helseslo]

I checked the fields with the formula that you suggested and the result shows that they are actually nulls (the formula is in “is_null” column):

[DylanM]

Hello @helseslo and @KonradAntczak, out of curiousity, if you recreated the control using a single value parameter rather than a multi-value parameter, does the NULL value filter as expected on both datasets?

I saw another user run into a limitation with a multiselect dropdown and NULL values and think the issue may be linked. 2 possible work-around options I can think of would be to replace actual NULL values with string values called ‘NULL’ and see if that resolves the issue. Alternatively, I am curious if you try changing the reserved value for select all in the parameter, if it would help resolve this issue. That is done in the advanced settings of the edit parameter modal.

I think the issue is coming from the functionality that handles select all for the multi-value parameters. If you are checking for that value in a calculated field, you have to check for NULL. I think this may be causing an issue with the NULL value selection with that type of a parameter. Let me know if either of my suggestions help resolve the issue!

[helseslo]

Hello @DylanM, thank you for your answer!

The workaround you proposed with creating a new column and changing nulls to the string “NULL” worked! Regarding the use of a single-value parameter, unfortunately, it’s not possible for our intended functionality. But I checked out of curiosity and indeed, filtering works correctly in that case However, changing the Custom value did not work in this instance.

Once again, thank you very much for your help!

[DylanM]

Hello @helseslo, I am happy to hear the work-around worked and it is also good to know that the issue is being caused by the multi-value parameter option. I will make sure that I tag this topic appropriately and archive it for the support team so the bug is documented.

Thank you for your detailed feedback and timely response!