Error: Can't determine relationships between the fields

[jmerunka]

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

[Anonymous]

@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/...

Regards,
Lydia

[jmerunka]

Stacked 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)

Visual

[Anonymous]

@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

[jmerunka]

Visual in my previous post was OK, because the link via Status calc was removed:
a diagram with a removed link

When link is activated again:
a diagram with an activated link

it breaks for opportunities:

status_calc for opp
status_calc for sales

where Actual = status 1 and Backlog = status 2, Pipeline = status 3 and Unknown = status 4

A chart for Opportunities should not be empty and should be combined with sales in a stacked bar chart.

I am not sure if I can post any real tabular data here. I am still in a trial period. 🙂

[jmerunka]

When I link Dim.Status_calc with :

1) opportunity - it works
2) sales - it works
3) opportunity & sales - it does not work

[Anonymous]

@jmerunka,

It is difficult for us to provide you suggestions without PBIX file. You can make a PBIX file with dummy data, and send me the PBIX file via Private Message.

Regards,
Lydia