Measure Filtering Scenario

[JustSayJoe]

I need help getting around an issue I have with a measure getting filtered incorrectly for my purposes. See below scenario.

TABLE_A

Includes columns: [Email Address], [Date], [Order ID], [Product]

Measure is [Returning Customers] and has the following code...

  COUNTROWS(FILTER(VALUES('TABLE_A'[Email Address], DISTINCTCOUNT('TABLE_A'[Order ID]) > 1 && DISTINCTCOUNT('TABLE_A'[Product]) > 1))

This measure works great when I'm filtering by the Date, but when I filter by the Product it does not display the correct values. I get the why of it happening, but I can't figure out how to make this measure still give me the correct number of returning customers when filtered by Product only. Any help here would be very much appreciated. Thanks!

[parry2k]

@JustSayJoe you are covered, glad it was helpful.

[parry2k · Accepted solution]

@JustSayJoe have you seen this article?

I'm sure it will help to solve.

[JustSayJoe]

Thank you! That article was what I've been searching for over the past 4 days.

[parry2k]

@JustSayJoe share the sample data and the expected output.

[JustSayJoe]

Date	Order ID	Email Address	Product
8/1/2023	1	[email protected]	Thing1
8/2/2023	2	[email protected]	Thing2
8/3/2023	3	[email protected]	Thing3
8/4/2023	4	[email protected]	Thing4
8/5/2023	5	[email protected]	Thing5
8/6/2023	6	[email protected]	Thing1
8/7/2023	7	[email protected]	Thing2

Expected Outcome of the [Returning Customers] Measure...
When I filter my page by Product for "Thing1", the Returning Customers should be 0.

When I filter my page by Product for "Thing2", the Returning Customers should be 0.

When I filter my page by Product for "Thing3", the Returning Customers should be 1 (because [email protected] previously purchased another Product on 8/1/2023).

When I filter my page by Product for "Thing4", the Returning Customers should be 0 (even though [email protected] has purchased two different products, Thing4 was their first purchased product, so they were not a returning customer yet).

When I filter my page by Product for "Thing5", the Returning Customers should be 1 (because [email protected] previously purchased another Product on 8/4/2023).