Create column base on count of another table with some conditions

[tanat_inc]

I have 2 table with below

How should I add column to "fact_customer_profile" for visit_count_facebook ?  I have success with:

countrow(
  filter( fact_customer_visit, fact_customer_visit[cusotmer_id] = fact_customer[customer_id] && fact_customer_visit[mkt_channel] = "Facebook"
)

But it will count duplicate values as two. Also when I experiment to make it count distinct on visit_id, it always fails with infinite duration calculation.

[Ashish_Mathur]

Hi,

Assuming the ID's in Table1 will never repeat, create a relationship (Many to One and Single) from Table2 to Table1.  To your visual, drag the fields from Table1.  Write this measure

Measure = distinctcount('Table2'[Employee ID])

Hope this helps.

[tanat_inc]

Could you provide the code to add calculated column instead? 

Actual dataset is 3GB in size and I want to create multiple calculated column like this for customer segmentation purpose.

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file and show the expected result.

[tanat_inc · Accepted solution]

I can make it with this code already :

countrows(
  summarize(  

    filter(

      fact_customer_visit,

      fact_customer_visit[cusotmer_id] = fact_customer[customer_id]

        && fact_customer_visit[mkt_channel] = "Facebook"

    ),

    fact_customer_visit[cusotmer_id],

    fact_customer_visit[mkt_channel]
)

It's adapt from the code provided by performance analyzer after did what you told me to do. Not sure if it's the best way or not, but thanks to the idea you provided.