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

[Pico]

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

[rwunderlich · Accepted solution]

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]

Thank you for the clarification, works perfectly.

Please let me correct a Typo 

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