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

[Anonymous]

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 Diagram

[v-xiaoyan-msft · Accepted solution]

Hi@Anonymous ,

'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.

[DataZoe]

@Anonymous 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').