Forum Discussion
Error: Can't determine relationships between the fields
Hello all,
I have been given a task to create a report based on our company source data in Power BI
Unfortunatately my company has an absence of proper DWH and BI software and reports are therefore based on SSAS Cubes linked to Excel.
Relationships schema
Because I have to combine data from two SSAS Cubes (sales and opportunity) I created 'Dimensions' between them.
Even though all but one 'dimensions' contain just one column ( because of a time pressure) , it had worked pretty well until I added the last dimension (Dim_Status_calc).
If I try to use this dimension to filter data from SSAS Cubes, I get an error : Can't determine relationships between the fields.
The problematic relationship seems to be between the fields : sales.Status_calc (n:1) Dim_Status_calc.Status_calc (1:n) opportunity.Status_calc
sales.Status_calc = IF(sales[order invoice.is delivered]="True";1;IF(sales[order invoice.is delivered]="False";2))
sales.Status_calc values = (1,2)
Dim_Status_calc.Status_calc = DISTINCT(UNION(ALL(opportunity[Status_calc]);ALL(sales[Status_calc]))
Dim_Status_calc.Status_calc values = (1,2,3,4)
opportunity.Status_calc= IF(opportunity[opportunity.opportunity status]="open";3;4)
opportunity.Status_calc values = (3,4)
Relationship between these 3 fields unfortunately does not allow to filter data in both tables and I do not know why.
Thanks for your help. :)
6 Replies
jmerunka,
What fields of these three tables you use to create visual? In your scenario, you may need to combine required fields using Merge Queries feature in Query Editor of Power BI Desktop, then create visual using these fields.
There is a similar thread for your reference.
http://community.powerbi.com/t5/Desktop/Error-Can-t-determine-relationships-between-the-fields/td-p/173492
Regards,
LydiaStacked bar chart
Y- axis - 1 field - Dim_salesperson.Dim_sales person name
Measures - 2 fields - sales.price EUR , opportunity.total weighted EUR
Legend - 1 field - Dim_Status_calc.Dim_Status_calc
The desired state is that each shown measure should have two different colored bars based on its Dim_Status_calc.Dim_Status_calc.Measures in chart
sales.price EUR - two colored bars ( where Dim_Status_calc = 1 or where Dim_Status_calc =2)
opportunity.total weighted EUR - two colored bars ( where Dim_Status_calc = 3 or where Dim_Status_calc =4)
(Visual is based on current model which uses 3 measures instead of 2 and Dim_Status_calc =4 is filtered out)
jmerunka,
In your visual, I don't see any error message, could you please post expected result in table format? And is there any possibility that you can share the PBIX file for me the check?
Regards,
Lydia
