Measure causing unrelated rows to show in table

[RobbLewz]

I have two tables Dim Pot which is a one to many link to FactPotTransactions.

DROP TABLE IF EXISTS dbo.DimPot;
CREATE TABLE dbo.DimPot
	( Id INT IDENTITY (1,1) PRIMARY KEY
	, PotNo VARCHAR(10) NOT NULL
	, Amount DECIMAL(19,2) NOT NULL);
GO

INSERT	dbo.DimPot
		( PotNo, Amount )
VALUES	('5A', 300),
		('7B', 500),
		('10X', 1000);
GO

DROP TABLE IF EXISTS dbo.FactPotTransactions;
CREATE TABLE dbo.FactPotTransactions
	( TransId INT IDENTITY(1,1) NOT NULL PRIMARY KEY
	, PotNo VARCHAR(10) NOT NULL
	, Amount DECIMAL(19,2) NOT NULL
	, Auth VARCHAR(100) NOT NULL
	, TransDate DATE NOT NULL DEFAULT(GETDATE())
	);
GO

INSERT	dbo.FactPotTransactions
		( PotNo, Amount,  Auth)
VALUES	( '5A', 150, 'John Baker' ),
		( '5A', 50, 'John Baker' ),
		( '5A', 100, 'John Baker' ),
		( '7B', 300, 'Fred Jones' ),
		( '7B', 200, 'Fred Jones' ),
		( '10X', 500, 'Lisa Howells' );
GO

I want a simple table that shows the pot amount and the sum of all the transactions so far, but most importantly the amount remaing in the pot.

Spent = SUM (FactPotTransactions[Amount] )
Outstanding = [Spent] - SUM (DimPot[Amount] )

When I add the amount, then it is bringing in people who are not assocaited with a pot

The first line shows Fred Jones with an oustanding -300 but 5A isn't linked to any records with this person?

Why does SUM (DimPot[Amount] ) somehow break the relationship thats setup in the mode??

[RobbLewz · Accepted solution]

It seems like the "solution" to this is enable filtering in both directions between the tables, even though thats not what I want as the fact table wont have transactions right away or at all for some pots.

There is a problem then, that if a row exists in DimPot, but has no trasnactions then it wont show in the table visual.

So you need to enable "show items with no data" in your filtered table, otherwise you will miss rows with no transactions.

Personally this feels like a bug - raised with Microsoft.

[Greg_Deckler]

@RobbLewz In the visual is PotNo coming from the DimPot table or FactPotTransaction table?

[RobbLewz]

DimPot