Forum Discussion

wnicholl's avatar
wnicholl
Resolver II
2 years ago
Solved

Measure not returning any values.

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" && ""))
Need Help
  • sevenhills's avatar
    sevenhills
    2 years ago

    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

     

3 Replies

  • sevenhills's avatar
    sevenhills
    Super User
    2 years ago

    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