Set expressions to exclude nulls and count distinct elements not working.

[rmadursk]

I have a set of data that looks like this:

Attested_Date	Service
3/31/2023	satellite01
3/31/2023	wbscvecc11
3/31/2023	wbscvecc11
12/31/2023	rtvspx01
12/31/2023	rtvspx01
6/30/2024	triprh701
6/30/2025	wbscvecm04
6/30/2025	wbscvecm04
12/31/2025	spcsgridn02
12/31/2025	spcsgridn02
12/31/2025	spcsgsub01
12/31/2025	spcsmtier01
12/31/2025	spcsmdata01
-	wbscvemp21
-	wbscvmdm01
-	wbscvmdm01
-	wbscvmgr01
-	wbscvmgr90

I need to know how many Distinct Service names have an Attested_Date.

I've tried the following based on other Community posts:

//Count({$<[Attested_Date] = {"=not isnull([ATTESTED_DATE] )"}>} DISTINCT([Service]))
//Count({$<[Attested_Date] -= "=Null()">} DISTINCT([Service]))
//Count({$<[Attested_Date] = {'*'}>} DISTINCT([Service]))
//Count({$<[Attested_Date] = {"=Len(Trim([Attested_Date])) > 0"}>} DISTINCT([Service]))
//Count({$<[Attested_Date] = {'*'}-{''}>} DISTINCT([Service]))

These all just report back the count of distinct services (14) when it should be 9.

This expression appeared to be working a couple of days ago but stopped for some reason.  I don't think it is the app since I created a new app with just this table and I get the same results.  The Null fields appear to be actual Null fields because they show up in Data Manager Preview Pane as Nulls.

[Lisa_P]

Try this:

Count({<Attested_Date={"=len(Attested_Date)>1"}>}Distinct Service)

[rmadursk]

Nope, same thing.  I'm really starting to wonder if there isn't something funny in the data that I can't see.  

Thanks,

[Vegar · Accepted solution]

I think you where almost there in one of your attempts. Try this:

Count({$<[Attested_Date] = {"*"}>} DISTINCT([Service]))

[BrunPierre]

Count({$<[Attested_Date] = {"=Len(Trim([Attested_Date])) > 1"}>} DISTINCT([Service]))

[marcus_sommer]

The simplest way to handle such scenarios is to replace the NULL with a real value within the data-model, for example with:

coalesce(Attested_Date, '<NULL>') as Attested_Date

or

if(len(trim(Attested_Date)), Attested_Date, '<NULL>') as Attested_Date

and then you could directly select <NULL> as value or using it as condition within a set analysis.

[rmadursk]

I considered this as an option but hadn't tried it yet since, at least in my mind, the option of just ignoring the NULLs seemed appropriate.  

The coalesce function is unique.  I am going to need to play around with that one.

Thanks,

Ron

[rmadursk]

This one works.  

I'm not going back to check all the ones that I tried previously but my original formula also works again.  There must have been an anomaly somewhere that I was unaware of and couldn't find.

Thanks for the help.

Ron

[rmadursk]

This one works also. It is very close to my original formula.  

I'm not going back to check all the ones that I tried previously but my original formula also works again.  There must have been an anomaly somewhere that I was unaware of and couldn't find.

Thanks for the help.

Ron

[barnabyd · Accepted solution]

G'day @rmadursk, I would take this one step further. What I think you are doing is counting how many distinct services there are that have been attested. I take it that being attested is an important business concept. Therefore, I would create a flag in the load script first:

if( isnull( Attested_Date ), 0, 1 ) as isAttested

Then the set analysis becomes trivial:

Count( {$<isAttested={1}>} DISTINCT([Service]) )

I hope this is a useful comment for someone.

Cheers, Barnaby.