Measure creating relationship issues / duplicate rows in table

[jameshoneywill]

Hi 

I have 3x dimension tables; dim_Customer, dim_Business, dim_Product
(dim_Business is a list of offices)
1x fact table; fact_sales
all three dimension tables relate to 3 different fields in fact_sales

I have a fully working measure - [billed_revenue]

When I produce a table with the columns; 'Office name, Customer name, billed_revenue', then everything works

I then need to create a 'gap analysis' of whether a customer is currently buying a specific product, is an ex-customer of it, or has never bought the product. 

I have this measure which works perfectly in the table but only with the columns from dim_customer (i.e. customer name, [gap apples]) when I bring in the Office name I get a row for every office name against each customer name. i.e. I have 5 office names in dim_Business and get 5 rows for every Customer name. 

I don't understand how the first measure can work and yet when used in the below measure it doesn't...
note - MAXDate is a calculated column in fact_sales

GAP Apples =

if ( CALCULATE(

[Billed Revenue],

FILTER(fact_Sales,

 fact_Sales[Period] = fact_Sales[MaxDate]), FILTER(dim_product,

dim_product[Product Name] = "Apples"))

> 0,

"Yes",

if ( CALCULATE(

[Billed Revenue],

FILTER(dim_product,

dim_product[Product Name] = "Apples"))

 > 0, "Lost",

"Never"

))

[jameshoneywill]

fixed it by changing "Never" to blank()

[amitchandak]

@jameshoneywill , please try approaches like

Customers who purchased category 1 not category 2: https://youtu.be/Io1SOY4oF6I

or

And for Selected Values, All selected values are present: https://youtu.be/X5T4rIZovHk

[jameshoneywill]

Thanks, I'm very similar to this 'Customers who purchased category 1 not category 2' but it's when I bring in the Office name that the rows duplicate. 
I've narrowed it down to when the if statement gets used, ive also tried switch and have the same result.
I've also tried each step as an individual measure which works, its just when they are combined in the if or switch

[foodd]

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.