splitting one table into two - relationships don't work

[Anonymous]

I'd like to create a simple table in a report which has 3 columns

1. Sales contract name (held in Sales Contract table)
2. Agency Primary (held in Sales Contract Agency Table, with a "primary" flagged on the row)
3. Agency Secondary (if there is any,held in Sales Contract Agency Table, with a "secondary" flagged on the row)

I have 2 data/tables: 

1. Sales Contract
2. Sales Contract Agency

The Sales Contract Agency table has a heap of agencies in it related to the Sales Contract ID. Some of the Agencies are a primary agency for the said sales contract, while others are secondary - but they are held in the same table. 

In PBI I have created one "Sales Contract Agency Primary" table, and filtered by "primary", and another table  "Sales Contract Agency Secondary" table, and filtered by "secondary" and have related both those tables back to the Sales Contract ID in the Sales Contract table. The relationships are good, so I have 3 tables:

1. Sales Contract
2. Sales Contract Agency Primary
3. Sales Contract Agency Secondary

Data from Sales Contract and EITHER ONE of "Sales Contract Agency Primary" or "Sales Contract Agency Secondary" can exist in a table, but not data from all three - I get a relationship error (and this is from the frown report)

"Cant display the data because PBI can't determine the relationship between two or more fields"

OData Error Message:
Not showing data for DataShape 'DS0' because it's not clear how these fields are related.

Any clues as to why this happens? 

[Greg_Deckler]

Can you supply some sample data (text), a screen shot of the table relationships and a sample of the visualization you are trying to achieve (obviously a mock-up of it). One thing to try, try switching the relationships to be filtered one way versus cross filtering in both directions and see if that resolves the issue (from primary and secondary to your main fact table).

[Anonymous]

Thanks for the reply - I tried switching from both to single but no cigar.

It seems to only occur when I load it into a SQL DB and use DirectQuery (occurs when using SQL as source, no matter if DQ or Import) - when I load in the excel files it works fine. I have the CSVs, PBIX and MSSQL bacpac files here: https://dl.dropboxusercontent.com/u/419424/pbitest.zip

here's some clean data that exhibits the issue when loaded into a DB

Sales Contract

SalesContractNumber	SalesContractID	StartDate	EndDate
50000	32E897C4-A6E5-453C-80F8-2BB269797668	28-11-2016 0:00	04-12-2016 0:00
50001	3BE5B249-67D1-4E20-B667-68BE7AC2EAD0	28-11-2016 0:00	04-12-2016 0:00
50002	88EA05EC-69A1-4050-9A03-FEFAE51CBE51	28-11-2016 0:00	04-12-2016 0:00
50003	1BBA8B5C-1A55-4168-BA31-E41D9FB4FCCF	28-11-2016 0:00	15-12-2016 0:00
50004	8BCF9EEE-575B-47C5-AE4A-E5914F155080	28-11-2016 0:00	04-12-2016 0:00
50005	0C2F3C0E-C3C4-4328-8E60-2B7F84F84AE7	28-11-2016 0:00	04-12-2016 0:00
50006	8F268AC4-D72D-4A37-A56F-356A99947120	05-12-2016 0:00	11-12-2016 0:00
50007	39BDDE5C-D150-4D5F-98F7-F086E7C3CC59	28-11-2016 0:00	25-12-2016 0:00
50008	CFEA5F10-3E44-4810-8BD5-51E830D5007F	05-12-2016 0:00	11-12-2016 0:00
50009	A4120A87-3E51-4772-9122-CC1225852457	05-12-2016 0:00	11-12-2016 0:00
50010	FA574D2A-DB10-49A1-968C-6B82DB64E021	05-12-2016 0:00	08-01-2017 0:00
50011	F0005343-CDD4-408F-B780-3D537C941A88	05-12-2016 0:00	11-12-2016 0:00
50012	E29E25AC-BABE-4A8B-ACCF-B0D9C4A6CA4D	05-12-2016 0:00	11-12-2016 0:00
50013	97D25190-CB7B-4AC0-8360-6D61B8EE6081	05-12-2016 0:00	11-12-2016 0:00
50014	3152263A-A687-4BA0-A1BB-A8ADF930FD30	19-12-2016 0:00	15-01-2017 0:00
50015	2793B2D1-FDD2-4816-8D9B-0E262EB058C6	12-12-2016 0:00	08-01-2017 0:00
50016	D8827889-F0A7-466D-8B92-50262166ED9F	08-12-2016 0:00	27-08-2017 0:00
50017	55F2AC01-B77B-4D78-B614-0006BC870737	26-12-2016 0:00	22-01-2017 0:00
50018	147D019A-BEFF-40C8-9826-C0A3E19F2F52	20-02-2017 0:00	20-03-2017 0:00
50019	933AB1B0-D5A2-42C2-AC2E-9093CF489062	13-12-2016 0:00	09-01-2017 0:00
50020	9A9F29B8-559D-4C19-BACA-B07DB3EC3E8F	12-12-2016 0:00	08-01-2017 0:00
50021	C63AFD7F-B0B9-443C-923E-9A15BB024432	12-12-2016 0:00	08-01-2017 0:00
50022	643DFF52-4766-4ED8-B93F-2120A030B4DF	12-12-2016 0:00	18-12-2016 0:00
50023	FC7A71CE-3E00-409C-BF7B-61CCE8DC3F28	12-12-2016 0:00	08-01-2017 0:00
50024	410D24BF-8872-4307-9144-C2F20CF6AF81	12-12-2016 0:00	08-01-2017 0:00
50025	C5C18B2F-E27D-42DB-82F7-A39011E7B9E0	12-12-2016 0:00	08-01-2017 0:00
50026	4997A35A-3BAD-473D-8B46-B9A64914CD58	12-12-2016 0:00	08-01-2017 0:00
50027	383D39FA-2300-4924-8CBF-D361C4A7CFBA
50028	16B5C99F-45D7-462C-B5AD-05EBE75FB777	13-12-2016 0:00	18-12-2016 0:00
50029	52A15FBF-21CB-47CF-B824-0DA62059FE4C	13-12-2016 0:00	18-12-2016 0:00
50030	8CA691B8-5486-4F77-8DDD-BE349464D9CF	13-12-2016 0:00	18-12-2016 0:00
50031	A7D6BF59-CA3E-450D-BB7F-78381348F05D	13-12-2016 0:00	25-12-2016 0:00
50032	7D844B4E-62AE-4A06-9E8D-ED44D4FFDE1C	13-12-2016 0:00	18-12-2016 0:00
50033	53064373-6EA2-4FE8-80B1-2948C30D73A5
50034	C610B674-8AA1-433A-89FD-AD51C4E672BB	14-12-2016 0:00	10-01-2017 0:00
50035	8A278608-DFC3-4388-90B1-FEEC518B884D
50036	3FFD15E0-0767-44AC-82F1-873794D327D5
50037	EA11ECD2-CE6B-48C0-97BD-47A2E98ABFC4
50038	0B0249E0-8CFC-40A3-B21D-36096EE88049	19-12-2016 0:00	15-01-2017 0:00
50039	51B22F13-1FFE-438F-915D-94B25D05DF1E	18-12-2016 0:00	14-01-2017 0:00
50040	EB8C76D0-F747-41E9-99B9-BB3D6C3C4DD9	19-12-2016 0:00	19-12-2016 0:00
50041	0C037EDE-2D5F-4313-BC7A-CFBC9F34C16E	19-12-2016 0:00	19-12-2016 0:00
50042	63D99E7B-8C5F-4608-AD53-003BD63E0E41	19-12-2016 0:00	19-12-2016 0:00
50043	8E246393-C2F1-4573-BD17-A65F89AB2AB0	21-12-2016 0:00	17-01-2017 0:00
50044	982112EE-C276-4847-92F7-E0F1CDD0E1C0	21-12-2016 0:00	23-12-2016 0:00

Agencies

SalesContractID	Type	AgencyName
0C037EDE-2D5F-4313-BC7A-CFBC9F34C16E	Primary	PILOT- Agency 1
147D019A-BEFF-40C8-9826-C0A3E19F2F52	Primary	Benefit Media
147D019A-BEFF-40C8-9826-C0A3E19F2F52	Secondary	Agency OIU
16B5C99F-45D7-462C-B5AD-05EBE75FB777	Primary	PILOT- Agency 1
3152263A-A687-4BA0-A1BB-A8ADF930FD30	Primary	Agency ABC
32E897C4-A6E5-453C-80F8-2BB269797668	Primary	PILOT- Agency 1
3BE5B249-67D1-4E20-B667-68BE7AC2EAD0	Primary	PILOT- Agency 1
51B22F13-1FFE-438F-915D-94B25D05DF1E	Primary	PILOT- Agency 1
52A15FBF-21CB-47CF-B824-0DA62059FE4C	Primary	PILOT- Agency 1
53064373-6EA2-4FE8-80B1-2948C30D73A5	Primary	AdvertCorp
55F2AC01-B77B-4D78-B614-0006BC870737	Primary	IKEN Worldwide
55F2AC01-B77B-4D78-B614-0006BC870737	Primary	BNE Media
55F2AC01-B77B-4D78-B614-0006BC870737	Secondary	BBC Limited
63D99E7B-8C5F-4608-AD53-003BD63E0E41	Primary	PILOT- Agency 2
643DFF52-4766-4ED8-B93F-2120A030B4DF	Primary	PILOT- Agency 1
7D844B4E-62AE-4A06-9E8D-ED44D4FFDE1C	Primary	PILOT- Agency 1
8BCF9EEE-575B-47C5-AE4A-E5914F155080	Primary	PILOT- Agency 2
8CA691B8-5486-4F77-8DDD-BE349464D9CF	Primary	PILOT- Agency 2
8E246393-C2F1-4573-BD17-A65F89AB2AB0	Primary	HELLO Worldwide
8E246393-C2F1-4573-BD17-A65F89AB2AB0	Secondary	POS Agency
8F268AC4-D72D-4A37-A56F-356A99947120	Primary	PILOT- Agency 1
933AB1B0-D5A2-42C2-AC2E-9093CF489062	Primary	OMX
933AB1B0-D5A2-42C2-AC2E-9093CF489062	Secondary
982112EE-C276-4847-92F7-E0F1CDD0E1C0	Primary	PILOT- Agency 1
C610B674-8AA1-433A-89FD-AD51C4E672BB	Primary	Port Agency
D8827889-F0A7-466D-8B92-50262166ED9F	Primary	Universal
EA11ECD2-CE6B-48C0-97BD-47A2E98ABFC4	Primary	AD Effects
EA11ECD2-CE6B-48C0-97BD-47A2E98ABFC4	Secondary	Port Agency
EB8C76D0-F747-41E9-99B9-BB3D6C3C4DD9	Primary	PILOT- Agency 1

 PrimarySecondary

[Greg_Deckler]

Thanks, I'll take a look and see if I can recreate the issue. Sounds like from what you describe it only occurs when using DirectQuery, correct?

[Anonymous]

Thanks Smoupre, I appreciate it. It appears to only occur when a SQL datasource is used, no matter if it's DQ or Import. It works fine as CSV file data source.... both examples as 2 pbix files are in the above zip. You'll need to put the bacpac in a "pbitest" MSSQL db on localhost or change the data source if the DB is located elsewhere.

[TM_Evan]

Was this ever resolved and if so how did you overcome this?