Forum Discussion
Measure on a measure
For reference, I am an insurance agent.
Goal: I need to count the number of entities (customers) where ActivePolicyCount=1
I have a table named "Entities" (essentially customers). Within that table I have the following measure; ActivePolicyCount:
ActivePolicyCount =
CALCULATE(
COUNTA(Policies[UID]),
Policies[Active]=TRUE(),PolicyLines[DisplayName]<>"Annuity"
)
This correctly calculates the number of active policies per client:
I've probably tried 4-5 different methods but I keep getting and error:
"A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed."
Any help would be appreciated
you can try below measure
Count_Customers_ActivePolicy1 = CALCULATE( DISTINCTCOUNT(Entities[CustomerID]), FILTER( Entities, [ActivePolicyCount] = 1 ) )Regards
sanalytics
5 Replies
ajohnso2Solution Supplier
If your Policies[Active] column is not a boolean type you should change to Policies[Active] = 1
Either way the dax should work now, for multiple filters use the required operator && (and) || (or) etc not ,
ActivePolicyCount = CALCULATE( COUNTA(Policies[UID]), Policies[Active] = TRUE() && PolicyLines[DisplayName]<>"Annuity" )If this helps can i get some free insurance? 😄
- Jai-Rathinavel
Super User
millercj If you just want to filter the table you can just Click the table visual on the right you can see Count of Activity measure just filter it to 1 and apply
Did I answer your question? If yes, please mark my post as a solution.
Thanks,
Jai
- sanalytics
you can try below measure
Count_Customers_ActivePolicy1 = CALCULATE( DISTINCTCOUNT(Entities[CustomerID]), FILTER( Entities, [ActivePolicyCount] = 1 ) )Regards
sanalytics
That did it! Thank you!
- Bibiano_Geraldo
Hi millercj ,
Please try the bellow updated DAX:
CustomersWithOneActivePolicy = CALCULATE( COUNTROWS(Entities), FILTER( Entities, CALCULATE( COUNTA(Policies[UID], Policies[Active] = TRUE(), PolicyLines[DisplayName] <> "Annuity") ) = 1 ) )
