Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Pico
Partner - Contributor III
Partner - Contributor III

list of objects in [Feld] that have Null as [Other Field]

Hi all, 

I have 2 fileds: AccountID and Scope. Some Account does not have a Scope (null in scope) and I would like to find them. I can work only in Front End, so no load script can be used.

E.g.:

Account Scope
1234 Scope 1
2345 -
345 Scope 2

 

I managed to create a KPI that show how many Account with null scope I have, with the formula

count({1}distinct AccountID) - count({1<[Scope]-={"-"}>} distinct AccountID)

(note that the formula "count({1<[Scope]={"-"}>} distinct AccountID)" gives a wrong answer: always 0.

 

Now I would like to see WHAT are those AccountID.

I tried a table with the dimension column "AccountID" and the measure column "Count({<Scope-={"*"}>} AccountID)", but it doesn't work

Neither does the measure column "Only({<Scope={"-"}>}  AccountID)" (it gives only one one full-null row)

Neither "IF(Count({<Scope={"*"}>} AccountID))=0, AccountID)" (it gives only one one full-null row)

 

Thank you for your help

Labels (3)
1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Note that "-" is not the true value of your Scope, "-" is a display placeholder for true null. A correct set for selecting AccountIDs with Scope=null would be:

<AccountID={"=IsNull(Scope)"}>

A count of Accounts with null Scope would be:

Count({<AccountID={"=IsNull(Scope)"}>} Distinct AccountID)

In a table, use AccountID as Dimension and the Count expression as Measure. 

-Rob

View solution in original post

2 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Note that "-" is not the true value of your Scope, "-" is a display placeholder for true null. A correct set for selecting AccountIDs with Scope=null would be:

<AccountID={"=IsNull(Scope)"}>

A count of Accounts with null Scope would be:

Count({<AccountID={"=IsNull(Scope)"}>} Distinct AccountID)

In a table, use AccountID as Dimension and the Count expression as Measure. 

-Rob

Pico
Partner - Contributor III
Partner - Contributor III
Author

Thank you for the clarification, works perfectly.

Please let me correct a Typo 

Count({<AccountID={"=IsNull(Scope)"}>} Distinct AccountID)