Sum Where Field Is Null in Data Model

[Anonymous]

Below is my data model:

If have created a straight table with the following fields:

I want to do some basic DQ calculations. Such as SUM AMOUNT USD when Contact ID is NOT NULL and Lead ID IS NULL.

Not having much luck after some research.

Any help is appreciated.

[sunny_talwar]

May be this:

Sum({<[Contact ID] = {"=Len(Trim([Contact ID])) > 0"}, [Lead ID] = {"=Len(Trim([Lead ID])) = 0"}>}[Amount USD])

[Anonymous]

thank you for the replay Sunny but this did not work. Not data is returned with this expression.

I can confirm there are records where CONTACT ID is NOT NULL and LEAD ID IS NULL

[sunny_talwar · Accepted solution]

How about this:

Sum({<[Contact ID] = {"=Len(Trim([Contact ID])) > 0 and Len(Trim([Lead ID])) = 0"}>}[Amount USD])

[Anonymous]

Brilliant. Thank you.

[Anonymous]

Sunny - Does the syntax change if I want both fields to be blank? Trying to apply by changing > to = but it is not working

[Anonymous]

another idea:

--> Lead ID null(), Contact ID not null()

=sum({($-<[Lead ID]={"*"} >) * (<[Contact ID]={"*"} >)} [Amount USD])

--> both fields null():

=sum({($-<[Lead ID]={"*"} >) * ($-<[Contact ID]={"*"} >)} [Amount USD])

--> both fields not null():

=sum({(<[Lead ID]={"*"} >) * (<[Contact ID]={"*"} >)} [Amount USD])

[sunny_talwar]

This is not working?

Sum({<[Contact ID] = {"=Len(Trim([Contact ID])) = 0 and Len(Trim([Lead ID])) = 0"}>}[Amount USD])

You wanted rows where both Contact ID and Lead ID is null? Is that possible?

[Anonymous]

That is not working. I've tried several variations

Below is proof that these rows exist:

[sunny_talwar]

How about this:

Sum({<[Opportunity ID] = {"=Len(Trim([Contact ID])) = 0 and Len(Trim([Lead ID])) = 0"}>}[Amount USD])