Forum Discussion

jmerunka's avatar
jmerunka
Frequent Visitor
8 years ago

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's avatar
      jmerunka
      Frequent Visitor
      8 years ago

      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's avatar
        Anonymous
        Not applicable
        8 years ago

        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