Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
tanat_inc
Helper II
Helper II

Create column base on count of another table with some conditions

I have 2 table with below

tanat_inc_0-1683339169014.png


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.

1 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.

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

Top Kudoed Authors