Power BI Exchange

Hi. Newbie here who has spent far too much time trying to find an answer so any help will be very much appreciated.

My aim is to count the number of unique Customer IDs in a table column. I can do this for an unfiltered column and I can do this for a filtered column but I'm struggling when I want to filter one column by one value and filter a separate column by two values.

The table below is a simple version. The real table has a lot more columns.

So, I want to count the unique 'Customer ID's for customers with either a 'blue' or a 'green' 'Product ID' who also have a Product Description of 'accept'. THe answer here should be 4 (everyone except customer 101).

When I do this I always end up with 5.

I've tried many variations of code but as way of an example here's one below. Any help or even just a point in the right direction would be great.

Measure = CALCULATE(DISTINCTCOUNT('Table'[Customer ID]), 'Table' [Product Description="accept", 'Table' [Product ID]="blue" || 'Table' [Product ID]="green" 

Customer ID	Product ID	Product Description	Company
100	blue	accept	ABC Co
100	blue	accept	ABC Co
100	red	accept	ABC Co
100	green	accept	ABC Co
101	red	postpone	DEF Co
102	blue	accept	XYX Co
102	red	accept	XYX Co
103	blue	accept	ABC Co
103	red	accept	ABC Co
103	green	accept	ABC Co
104	green	decline	DEF Co
104	blue	postpone	DEF Co

------------------------------
Tom Green
------------------------------

Hi Tom, try the following code - you were nearly there, but a couple of parenthesis out etc...

I think your counting above is incorrect.  So you are looking for description = Accept AND (product is is Blue OR product is Green).  There are only 5 rows that match that criteria, and 3 unique Customer IDs.  I've highlighted the rows in the image below




attached is the file.

------------------------------
Ben Howard, UK.

Please mark any answer as recommended if it helps you.
------------------------------

Original Message:
Sent: Apr 07, 2021 08:01 AM
From: Tom Green
Subject: DISTINCTCOUNT with filters help

Hi. Newbie here who has spent far too much time trying to find an answer so any help will be very much appreciated.

My aim is to count the number of unique Customer IDs in a table column. I can do this for an unfiltered column and I can do this for a filtered column but I'm struggling when I want to filter one column by one value and filter a separate column by two values.

The table below is a simple version. The real table has a lot more columns.

So, I want to count the unique 'Customer ID's for customers with either a 'blue' or a 'green' 'Product ID' who also have a Product Description of 'accept'. THe answer here should be 4 (everyone except customer 101).

When I do this I always end up with 5.

I've tried many variations of code but as way of an example here's one below. Any help or even just a point in the right direction would be great.

Measure = CALCULATE(DISTINCTCOUNT('Table'[Customer ID]), 'Table' [Product Description="accept", 'Table' [Product ID]="blue" || 'Table' [Product ID]="green" 

Customer ID	Product ID	Product Description	Company
100	blue	accept	ABC Co
100	blue	accept	ABC Co
100	red	accept	ABC Co
100	green	accept	ABC Co
101	red	postpone	DEF Co
102	blue	accept	XYX Co
102	red	accept	XYX Co
103	blue	accept	ABC Co
103	red	accept	ABC Co
103	green	accept	ABC Co
104	green	decline	DEF Co
104	blue	postpone	DEF Co

------------------------------
Tom Green
------------------------------

Thank you so much Ben. Really appreciate you taking the time to help me out. That does the trick. Thanks again.

------------------------------
Tom Green
------------------------------

Original Message:
Sent: Apr 07, 2021 10:19 AM
From: Ben Howard
Subject: DISTINCTCOUNT with filters help

Hi Tom, try the following code - you were nearly there, but a couple of parenthesis out etc...

I think your counting above is incorrect.  So you are looking for description = Accept AND (product is is Blue OR product is Green).  There are only 5 rows that match that criteria, and 3 unique Customer IDs.  I've highlighted the rows in the image below

 CALCULATE(
    DISTINCTCOUNT( 'Table'[Customer ID] ),
    'Table'[Product Description] = "accept"
        && ( 'Table'[Product ID] = "blue"
        || 'Table'[Product ID] = "green" )
)

attached is the file.

------------------------------
Ben Howard, UK.

Please mark any answer as recommended if it helps you.

Original Message:
Sent: Apr 07, 2021 08:01 AM
From: Tom Green
Subject: DISTINCTCOUNT with filters help

Hi. Newbie here who has spent far too much time trying to find an answer so any help will be very much appreciated.

My aim is to count the number of unique Customer IDs in a table column. I can do this for an unfiltered column and I can do this for a filtered column but I'm struggling when I want to filter one column by one value and filter a separate column by two values.

The table below is a simple version. The real table has a lot more columns.

So, I want to count the unique 'Customer ID's for customers with either a 'blue' or a 'green' 'Product ID' who also have a Product Description of 'accept'. THe answer here should be 4 (everyone except customer 101).

When I do this I always end up with 5.

I've tried many variations of code but as way of an example here's one below. Any help or even just a point in the right direction would be great.

Measure = CALCULATE(DISTINCTCOUNT('Table'[Customer ID]), 'Table' [Product Description="accept", 'Table' [Product ID]="blue" || 'Table' [Product ID]="green" 

Customer ID	Product ID	Product Description	Company
100	blue	accept	ABC Co
100	blue	accept	ABC Co
100	red	accept	ABC Co
100	green	accept	ABC Co
101	red	postpone	DEF Co
102	blue	accept	XYX Co
102	red	accept	XYX Co
103	blue	accept	ABC Co
103	red	accept	ABC Co
103	green	accept	ABC Co
104	green	decline	DEF Co
104	blue	postpone	DEF Co

------------------------------
Tom Green
------------------------------