Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Thank you for the clarification, works perfectly.
Please let me correct a Typo
Count({<AccountID={"=IsNull(Scope)"}>} Distinct AccountID)