Including a measure in a visual creates a cross join due to relationships in model

[karlk]

Hi,

I have Power BI report with the following data model:

Relationship Issue

The definition of the Total Sales measure is: Totol Sales = SUM(Sales[Order Total])

When I create a table visual with either Customer Name and Discount or Customer Name and Total Sales the output is as expected:

Customer and Discount % - OK
Customer Name	Discount %
Customer 1	5.00%
Customer 1	7.50%
Customer 1	10.00%
Customer 2	2.00%
Customer 2	4.00%
Customer 2	5.50%
Customer 2	9.00%

Customer and Total Sales - OK
Customer Name	Total Sales
Customer 1	5520
Customer 2	6000
	11520

I try and create a table visual with Customer Name, Discount % and Order No (from Sales) Power BI gives me an error saying it can't display the data because it can't determine the relationship between two or more fields.  This is fine and and I understand that it's behaving correctly.

However, when I create a table visual with Customer Name, Discount % and Total Sales (measure) the output looks like this:

Customer, Total Sales and Discount % - NOT OK
Customer Name	Discount %	Total Sales
Customer 1	2.00%	5520
Customer 1	4.00%	5520
Customer 1	5.00%	5520
Customer 1	5.50%	5520
Customer 1	7.50%	5520
Customer 1	9.00%	5520
Customer 1	10.00%	5520
Customer 2	2.00%	6000
Customer 2	4.00%	6000
Customer 2	5.00%	6000
Customer 2	5.50%	6000
Customer 2	7.50%	6000
Customer 2	9.00%	6000
Customer 2	10.00%	6000
		11520

Power BI seems to have cross joined Customer and Customer Discount in order to calculate the measure.  What I would like to know is:

1.  Why?  Is it to do with the context transition from row context to filter context when calling the measure?

2.  How can I practically avoid this situation.  

Any help is appreciated.

Thanks

Karl

[karlk · Accepted solution]

Hi,

Thanks for your comments.  I've solved the problem using the Performance Analyzer in Power BI to get the DAX.

It ran:

EVALUATE
    SUMMARIZECOLUMNS(
        'Customer'[Customer],
        'Customer Discount'[Valid From Date], 
        "Total Sales", '*** Measures ***'[Total Sales]
    )

So, this was effectively a cross join between Customer and Customer Discount with a filter that removes rows from the result where [Total Sales] returned BLANK.  However the definition of [Total Sales] would never return BLANK because its result is only filtered by Customer, not Customer and Customer Discount.  Because [Total Sales] would never return BLANK it does not remove rows from the result so the extra rows produced by the cross join remain.

Customer, Total Sales and Discount % - NOT OK
Customer Name	Discount %	Total Sales
Customer 1	2.00%	5520
Customer 1	4.00%	5520
Customer 1	5.00%	5520
Customer 1	5.50%	5520
Customer 1	7.50%	5520
Customer 1	9.00%	5520
Customer 1	10.00%	5520
Customer 2	2.00%	6000
Customer 2	4.00%	6000
Customer 2	5.00%	6000
Customer 2	5.50%	6000
Customer 2	7.50%	6000
Customer 2	9.00%	6000
Customer 2	10.00%	6000
		11520

In summary the context of the measure wasn't compatible with the context of the visual.

Thanks

Karl

[Tad17]

Hey @karlk 

It looks like you either need another measure, column, or table to relate order total and discount. Commenting mainly so I can see your desired result as mentioned by @JustJan  so I can further assess your problem and help in what way I can.

[JustJan]

Hi Karl, 

What would the desired result look like? There is also a Discount% in your Sales table.

At the moment you ask PBI to show all rows from the customer discount table for a customer and to show the total sales (measure) for each line. 

Jan