IF statement with set analysis excluding nulls

crichter14 — Wed, 19 Mar 2025 21:48:59 GMT

Oh, I have a mess. Here's the calculation from the end user:

=If(
count({<
[criteria1]={'value1'},
[criteria2] = {'value2'},
[criteria3]={'value3'},
[criteria4]={'value4', 'value5', 'value6', 'value7'},
[critera5]={'value8'},
[critera6]-={'value9'}
>} distinct value10) > 1
and
Len(Concat({<
[criteria1]={'value1'},
[criteria2] = {'value2'},
[criteria3]={'value3'},
[criteria4]={'value4', 'value5', 'value6', 'value7'},

[critera5]={'value8'},
[critera6]-={'value9'}

>} DISTINCT value11, ', ')
) > 8, 'Multiple Status', null())

They want to exclude nulls from their straight table.

Obviously since this is a measure (not a dimension) the exclude null check box won't work. I tried changing this to a dimension, except there's a Count in there so that doesn't seem to work. Since it's a text value they are going for the include zero check box won't work either.

Pulling my hair out. I'd like to keep this on the front end because the overall example is way too specific to include it in the data model. Any geniuses out there with a suggestion or two? Please save me from the creativity of others!

Re: IF statement with set analysis excluding nulls

anat — Tue, 24 Sep 2024 02:42:52 GMT

Can you try by removing else condition<null()>

Then enable suppress null values check box

And suppress zero values

Re: IF statement with set analysis excluding nulls

crichter14 — Thu, 17 Oct 2024 20:33:39 GMT

Thanks Anat! After several meetings we agreed upon a flag that wasn't as narrow for them. Between that and their filters we found a solution.

topic IF statement with set analysis excluding nulls in Data Movement & Streaming

IF statement with set analysis excluding nulls

Re: IF statement with set analysis excluding nulls

Re: IF statement with set analysis excluding nulls