Total Distinct count between two tables

[lbrosten]

I have two tables - Service Requests and Evaluations and want to count the number of unique accounts. I used Policy ClientID for Services and Policyholder from Evaluations. One account can have multiple entries in either table.

Measure 1 from the Service Requests table -  Count(distinct [Service Requests V1.Policyholder])     Answer: 38

Measure 2 from the Evaluations Table - Count(distinct [Policyholder] )     Answer:69

These two measures give me the distinct count for each of the tables. 

Now I want create a measure which answer the question:

How many distinct accounts are in the combined Service requests and Evaluations tables. The answer is 85.

[anthonyj]

Hi @lbrosten ,

You could try:

Count(distinct {$<[Policyholder]={"=[Policyholder] = [Service Requests V1.Policyholder]"}>}[Policyholder] )

See if that does the trick.

Thanks

Anthony

[vinieme12]

How are these two table associated in your data model? 

[lbrosten]

Thanks. It came back 22. Looking for 85.

[lbrosten]

 The Services table and the Risk Eval table are associated by field “Group Policy No-Policy Number”