Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Distinct count based on filtered columns in another table

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 

 

 

Need Help
  • johnt75's avatar
    johnt75
    4 years ago

    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) ) )

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , if these tables are joined with each other then

     

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

     

  • johnt75's avatar
    johnt75
    Super User
    4 years ago

    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) ) )