Forum Discussion
Measure creating relationship issues / duplicate rows in table
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
4 Replies
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
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 switchPlease 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-Forum/ba-p/963216
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.
fixed it by changing "Never" to blank()
