Measure not returning any values.

[wnicholl]

Hello,

I could use some help with creating a vitrual relationship between account and policy tables. I need to know how many accounts with policys have either "Active", "Expired", and "Blank".  This is the formula I wrote doesn't return any errors but it's also not returning any values.  Am I missing something in the formula?  Thanks in advance for any insights. 

# of Non-Insureds 2 = CALCULATE(TREATAS(VALUES(Policy[Account__c]), Account[Id]), FILTER(Policy, Policy[Policy_Type__c] = "Active" && "Expired" && ""))

[sevenhills · Accepted solution]

I think you are missing the expression, you want to do

CALCULATE(<expression>[, <filter1> [, <filter2> [, …]]])

try this..

 # of Non-Insureds 2 = 
   CALCULATE ( 
      -- you are missing this. Change to the calculation of your interest 
      SUM ( Account[Amount] ), 
      TREATAS( VALUES(Policy[Account__c]), Account[Id]),

      -- you want to check one of the values I believe 
       FILTER(Policy, Policy[Policy_Type__c] in {"Active", "Expired", ""} ) 
    ) 

Calculation can be sum, counta, countrows based on your needs!, Thanks

[wnicholl]

Got it... Thanks for your help! Works great.

[sevenhills]

Glad to hear that it worked 🙂