cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Count occurrence in a column that doesnt happen in another

Hello there,

 

I'd like to count how many times a value is shown in the Partner column as long as it doesn't appear in the Customer column. I've tried using something akin to that, but has not worked:

CALCULATE(COUNTROWS('Base name'), 'Base name'[Partner] <> 'Base name'[Customer])

 

Perhaps the reason on why this measure has not worked is the relationship between tables, however even using USERELATIONSHIP('Dimension table'[Name of the client], 'Fact table'[Partner]) I got nothing. In the example of the image, the table is a Fact type table, and its active relationship is between the Customer and another column from a dimension table.

 

Any thoughts on where I'm doing wrong?

 

pedrohp503_1-1651559595886.png

 

P.S.: In this table, my customer can be a partner as well.

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Anonymous , You should use a partner from the dimension. this should work

 

CALCULATE(COUNTROWS('Base name'),filter( 'Base name',  'Base name'[Partner] <> 'Base name'[Customer]) )

 

 

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

View solution in original post

4 REPLIES 4
philouduv
Resolver II
Resolver II

Hey @Anonymous ,

In your Dimension 'Dimension table'[Name of the client] write : 

test = CALCULATE(COUNTROWS('Base name'),
Filter(ALL('Base name'),
'Dimension table'[Name of the client] == 'Base name'[Patner]
&&
'Base name'[Patner] <> 'Base name'[Customer]))


Best regards,

 

Anonymous
Not applicable

Thanks, my friend

amitchandak
Super User
Super User

@Anonymous , You should use a partner from the dimension. this should work

 

CALCULATE(COUNTROWS('Base name'),filter( 'Base name',  'Base name'[Partner] <> 'Base name'[Customer]) )

 

 

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Anonymous
Not applicable

Thank you. It indeed worked 🤙

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.