cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
twikle
Frequent Visitor

Need help with dimension and fact tables. Unable to build queries for report.

I am building a report table that queries six tables. The primary table is Account which has information on patients (name, provider ID number, date of birth, etc.). The five other tables provide data related to the patient (health condition, case information, health care goals, and email campaign data). I set up the data model with the idea that the Account table is the center of the star with the other tables relating to it. It appears the Account table is a dimension table and the other tables are fact tables because of the one-to-many relationships shown in the diagram.

 

 I can build a report using the data in Account, Goal, and Case tables. I can build another report that has the data from Account and Identified Condition. I need a report that can pull data from Account, Case, Goal, and Identified Condition. When I try to do this I get the 'Can't display the data because Power BI can't deterine the relationship between two or more fields.' message.

 

The Account ID field in the Account table is tied to the corresponding Account ID fields in the other tables.

 

The data is from Salesforce Objects pulled from Salesforce into Power BI through the connector.

 

Currently at a loss on how to fix this. Any suggestions?

Data DiagramData Diagram

1 ACCEPTED SOLUTION
v-caitlyn-mstf
Community Support
Community Support

Hi@twikle ,

 

'Can't display the data because Power BI can't determine the relationship between two or more fields.'

You are trying to use two or more fields from tables that are not related. You need to remove the unrelated fields from the visual and then create a relationship between the tables. Once you have done this change, you can add the fields back to the visual. Refer: Create and manage relationships in Power BI Desktop 

 

In this case,  The table is on many side of the relationship and it cannot filter another table  (which is on one side). On relationship, change cross filter direction to both and it should work. 

You can refer this simliar issue: Can-t-determine-relationship-between-fields

 

Best Regards,
Caitlyn Yan

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-caitlyn-mstf
Community Support
Community Support

Hi@twikle ,

 

'Can't display the data because Power BI can't determine the relationship between two or more fields.'

You are trying to use two or more fields from tables that are not related. You need to remove the unrelated fields from the visual and then create a relationship between the tables. Once you have done this change, you can add the fields back to the visual. Refer: Create and manage relationships in Power BI Desktop 

 

In this case,  The table is on many side of the relationship and it cannot filter another table  (which is on one side). On relationship, change cross filter direction to both and it should work. 

You can refer this simliar issue: Can-t-determine-relationship-between-fields

 

Best Regards,
Caitlyn Yan

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

DataZoe
Super User II
Super User II

@twikle Did you have a measure created? Sometimes they will drive the context properly. Such as Cases = countrows('Case') and Goals = countrows('Goal') and Identified Conditions = countrows('Indentified Conditions').  

Respectfully,
DataZoe


See my reports and blog at https://www.datazoepowerbi.com/

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.