Forum Discussion
Measure Filtering Scenario
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!
5 Replies
JustSayJoe share the sample data and the expected output.
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).
Thank you! That article was what I've been searching for over the past 4 days.
JustSayJoe you are covered, glad it was helpful.
