Skip to main content
cancel
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.

Reply
jameshoneywill
Frequent Visitor

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

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




4 REPLIES 4
jameshoneywill
Frequent Visitor

fixed it by changing "Never" to blank()

amitchandak
Super User
Super User

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

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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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