Distinct count based on filtered columns in another table

[Anonymous]

Hi,

I need help with following scenario:

There are two tables AA and BB that have customer IDs in column "contact_id" and "id" respectively.

I need the distinct count of contact_id from Table-AA for who status = delivered and 

Consent = TRUE in Table-BB

Table-AA

Table-BB 

[johnt75 · Accepted solution]

You could create a measure like

Num delivered & consent =
var delivered = CALCULATETABLE( VALUES(AA[contact_id]), AA[status] = "delivered")
var consent = CALCULATETABLE( VALUES(BB[id]), BB[consent] = TRUE())
return COUNTROWS( DISTINCT( INTERSECT( delivered, consent) ) )

[amitchandak]

@Anonymous , if these tables are joined with each other then

calculate(count(AA[contact_id ]), filter(AA, [status] = "delivered", filter(BB, [Consent] = TRUE () ) )