Boolean measure breaks relationship?

[databubble]

Hi

I have tables DimSalesperson and FactSales. They are related by SalesPerson

DimSalesperson:

Salesperson	TargetTotalSales
Amit	4000
Barbara	5000
Carolina	6000
Deng	7000

FactSales:

InvoiceNum	SalesDate	SalesPerson	GrossInvoice	SalesTax
1	23/3/2016	Deng	758.85	92
2	29/3/2016	Barbara	154.93	98.97
3	5/4/2016	Barbara	651.56	62.97
4	14/4/2016	Deng	262.2	23.63
5	24/4/2016	Amit	674.17	94.68
6	30/4/2016	Barbara	468.93	26.42
7	8/5/2016	Carolina	256.9	89.74
8	17/5/2016	Deng	939.97	97.21
…etc...	…etc...	…etc...	…etc...	…etc...

I added three measures to FactSales:

• NetAmt = sum('Fact'[GrossInvoice]) - sum('Fact'[SalesTax])
• DiffToSalesTarget = [NetAmt]-min('Fact'[SalesTarget])
• MetSalesTarget = [NetAmt] > min('Fact'[SalesTarget])

And I have a basic table visualistion showing total netAmt for each SalesPerson. (I have included Salesperson twice - once from Dimension and once from Fact - to clarify the later problem)

NetAmt	SalesPerson	Salesperson
4173.67	Amit	Amit
5905.81	Barbara	Barbara
2249	Carolina	Carolina
4262	Deng	Deng

I can add TargetTotalSales and DiffToSalesTarget and it behaves as expected - the values are calculated in context.

NetAmt	SalesPerson	Salesperson	TargetTotalSales	DiffToSalesTarget
4173.67	Amit	Amit	4000	173.67
5905.81	Barbara	Barbara	5000	905.81
2249	Carolina	Carolina	6000	-3751
4262	Deng	Deng	7000	-2738

However if I add MetSalesTarget, for each sales person in the Fact table, I get a line for every SalesPerson in the Dimension table. It is as if the relationship no longer exists.:

NetAmt	SalesPerson	Salesperson	TargetTotalSales	DiffToSalesTargget	MetSalesTarget
4173.67	Amit	Amit	4000	173.67	TRUE
	Amit	Barbara	5000		FALSE
	Amit	Carolina	6000		FALSE
	Amit	Deng	7000		FALSE
	Barbara	Amit	4000		FALSE
5905.81	Barbara	Barbara	5000	905.81	TRUE
	Barbara	Carolina	6000		FALSE
	Barbara	Deng	7000		FALSE
	Carolina	Amit	4000		FALSE
	Carolina	Barbara	5000		FALSE
2249	Carolina	Carolina	6000	-3751	FALSE
	Carolina	Deng	7000		FALSE
	Deng	Amit	4000		FALSE
	Deng	Barbara	5000		FALSE
	Deng	Carolina	6000		FALSE
4262	Deng	Deng	7000	-2738	FALSE

What am I doing wrong? 

Many thanks

Liz

[Vvelarde]

@databubble

Hi, applied a visual filter and will work.

[databubble]

Hi

Thank you for helping with my problem.

I see a filter gives the results I want, but it feels like a workaround to a problem in my measure. I am new to Power BI and not sure what is good practice! Is it usual to generate and filter out the "wrong" results ?

Liz

[v-yuezhe-msft]

@databubble,

Generally, we don’t drag SalesPerson fields of both table to create a visual. We only drag SalesPerson field from either DimSalesperson table or FactSales table to visuals in Power BI, this way, expected result will return.

Regards,