Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Frequent Visitor

Measure creating relationship issues / duplicate rows in table


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 =

[Billed Revenue],
 fact_Sales[Period] = fact_Sales[MaxDate]), FILTER(dim_product,
dim_product[Product Name] = "Apples"))

> 0,
[Billed Revenue],
dim_product[Product Name] = "Apples"))
 > 0, "Lost",

Frequent Visitor

fixed it by changing "Never" to blank()

Super User
Super User

@jameshoneywill , please try approaches like

Customers who purchased category 1 not category 2:




And for Selected Values, All selected values are present:

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

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).

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

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

Helpful resources

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.


Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.