Power BI can't determine relationship between fields

[kaushikmakadia]

Hi there,

I am getting Power BI can't determine relationship between fields error for below my table structure :

TABLENAME: Story
SELECT 'INC911' as Incident, 'INV999' as Inves
UNION ALL
SELECT 'INC911' as Incident, 'INV999' as Inves
UNION ALL
SELECT 'INC814' as Incident, 'INV888' as Inves
UNION ALL
SELECT 'INC715' as Incident, 'INV777' as Inves
UNION ALL
SELECT 'INC614' as Incident, NULL as Inves
UNION ALL
SELECT 'INC514' as Incident, NULL as Inves

TABLENAME: Investigation

SELECT 'INV999' as InvesID, 'LABC' as Location
UNION ALL
SELECT 'INV888' as InvesID, 'LXYZ' as Location
UNION ALL
SELECT 'INV777' as InvesID, 'LOPQ' as Location

TABLENAME: Incident
SELECT 'INC911' as IncidentID, 'Closed' as Status
UNION ALL
SELECT 'INC814' as IncidentID, 'Pending' as Status
UNION ALL
SELECT 'INC715' as IncidentID, 'Open' as Status
UNION ALL
SELECT 'INC614' as IncidentID, 'Open' as Status
UNION ALL
SELECT 'INC514' as IncidentID, 'Pending' as Status

TABLENAME: Actions

SELECT 'WO901' as WorkOrderID, 'TP1' as WorkOrderType, 'INV999' as InvestWOID
UNION ALL
SELECT 'WO902' as WorkOrderID, 'TP2' as WorkOrderType, 'INV999' as InvestWOID
UNION ALL
SELECT 'WO800' as WorkOrderID, 'TP3' as WorkOrderType, 'INV888' as InvestWOID
UNION ALL
SELECT 'WO700' as WorkOrderID, 'TP4' as WorkOrderType, 'INV777' as InvestWOID

Relationship is 

1) Story.Incident <---> Incident.IncidentID
2) Story.Inves <---> Investigation.InvesID
3) Actions.InvestWOID  <---> Investigation.InvesID

To create this error drag-and-drop fields into Power BI table visual : Story.Incident, Story.Inves, Incident.IncidentID, Investigation.InvesID and Actions.InvestWOID

So last column Actions.InvestWOID gives this relationship error in table visual.

NOTE this model source is Analysis Services

Please help? Thanks you!

[kaushikmakadia]

Anyone please help ? I need this solution for business user sooner.

Highly appreciate.

NOTE relationships are in both directions.

[sp8]

Sorry, I just noticed that Incident.IncidentID also has a 1-way relationship and therefore doesn't really have a relationship with the other tables. 

You're starting with the story table, so the story table must be able to filter everything that you're adding in order for things to work. Otherwise, there's not really a relationship backwards. Could be that too.

1) You can try setting all of the relationships to both.

2) You can try creating a column that pulls in the related values and then using it

In the story table, you'd add: Incident = related(Incident) and then that's what you'd put in your table for the incident column.

[kaushikmakadia]

I have set all relationship to both and still same error :

pls help

[kaushikmakadia]

Can I ask something pls  using above SQL queries are you able to provide or upload a PBIX that will resolve this error? Thx

[sp8]

When you put InvesID in the table instead of InvestWOID, does it work or give you the same error?

[kaushikmakadia]

No error with Investigation.InvesID in visual table. Only when any column from Action WO table drag-and-drop get the error.

Please advise

[sp8]

1) Your issue is most likely that you're using single between Investigation.InvesID and Story.Inves.

The Story table can't filter the Investigation table because you've set it to single so only the investigation table can filter story. I bet that if you put Investigation.InvesID in your table instead of Actions.InvestWOID, you'll get the same error. (Trying that will help you prove that it's not the relationship between the Investigation and Actions table that's the issue.)

To fix it, switch the relationship to both.

2) I just take a screenshot with the snipping tool/snip & sketch (built into windows) and then hit ctrl+v to paste it into the comments here.

[kaushikmakadia]

Thanks. Still same error after set that relationship to both :

Any idea please?

[sp8]

A picture of the relationships might be helpful.

1) What kind of realtionships are they (many/1, 1/1...)?

2) What direction of filtering do you have (single/both)?

   If you have single direction filtering on some of the relationships, they might not be able to "jump" from table to table properly.

3) Are all tables in the same (import/DQ) mode? 

[kaushikmakadia]

Thanks. See below response

1) Story.Incident <---> Incident.IncidentID is M:1 and single
    Story.Inves <---> Investigation.InvesID is M:1 and single
    Actions.InvestWOID  <---> Investigation.InvesID is M:1 and single

2) For this forum simplicity there are T-SQL queries as above. But actual report get data from SSAS tabular data model.

3) Can I know how to attach picture or PBIX file?

Any idea pls?