unable to create relationship in power bi desktop

[Charu]

Hi Everyone,

I need to create the relationship between  tableA to TableB and TableB to TableC and TableB to TableD I'm getting the below error message

[Thejeswar]

Hi @Charu,

This error is because introducing this join would create a loop kind of this in your datamodel there by resulting in ambiguity of the tables usage.

In this, when you introduce a relation between Table B and Table D, it's morelikely that it would result in a loop. The Case might be your Table C and Table D are already related. Since your Table B and Table C are related, if you introduce a new join between Table B and Table D, that will result in a loop as Table B and Table D are already related by an indirect relationship through Table C

Regards,

Thejeswar

[Charu]

HI @Thejeswar

I think I confused you, for your better understanding

I'm trying to create  the relationship between 4tables

1.Actuals(employee id will repeat)

2.Target(employee id will be unique)

3.Calendar

4.Employee master

1.Actuals and target should create the relationship in KPI visual using Actuals table field in measure and Target table field in Target measure.so I need this relationship.

Likewise other sets of tables I need to create the relationship, for example, customer table with customer target table and the Customer target table with Calendar and then Customer target table with employee master table.

2. In one Report page, I need date filter and employee id filter, so I need to create the relationship with calendar and employee master table.

So when I filter employee Id It should filter both Actuals KPI as well as Customers KPI visual.

Thank you

[v-yuezhe-msft]

@Charu,

You can make inactive relationship between the two problem tables, then use USERELATIONSHIP function for the inactive relationship. There are some similar threads for  your reference.

http://community.powerbi.com/t5/Desktop/Ambiguity-in-a-direct-active-relationship-between-fact-and/t...
http://community.powerbi.com/t5/Desktop/you-can-t-create-a-direct-active-relationship-between-Survey...

Regards,
Lydia

[Thejeswar]

@Charu,

ya actually you can use USERELATIONSHIP() in the measures wherever there is not a direct active relationship between tables

Regards,

Thejeswar

[Charu]

Hi @Thejeswar @v-yuezhe-msft

I guess the thread you have shared is not exactly what I'm facing.

Just an example here

1. In KPI visual two columns we need to use in that indicator field I'm using count of customer name from customer actual table and

in the target field, I'm using SUM of a target from the other table.

2. In another KPI VISUAL Indicator field SUM of Transaction value from sales actuals table and the target field SUM of sales target from the sales target table.

in one report page, I need to keep date filter for these two tiles so I need the common table to link the date field from the two tables 

When I use the below  measure the target is not filtering based on the employee selection

1.CountCustomer = CALCULATE(COUNT(cust_table[Customer_name]),

USERELATIONSHIP(cust_target[Employee_ID],cust_table[employee_id]))

2.Cust_target =CALCULATE(SUM(cust_tgt[Target]), USERELATIONSHIP(cust_target[Employee_ID],cust_table[employee_id]))

how should I use this  USERELATIONSHIP option here either for above measure or

there is not a direct active relationship between tables like Calendar date field and Cust_target date field / Employee_master table employee id field and Cust_target employee id field.

 Please guide me where should I use USEREALTION function here.

Two tiles from other tables and the filter here should common for both the tile so without creating the relationship with the filter tables how the two tiles will get filtered?

[Thejeswar · Accepted solution]

Hi @Charu,

Can you try changing your model to the way it is shown below and try if it works.

[Charu]

HI @Thejeswar

Thanks this data model works well.

Thank you

[Thejeswar]

Hi @Charu,

If you can share a pbix file with similar data model, I will be useful to help further.

Otherwise it's quite  difficult to assisst on this

[Charu]

Hi @Thejeswar

So how should I create the relationship between those as it's really mandatory?

Actual table and target table need to create the relationship so that I could use those two table fields.

and for filter purpose, I need to connect Calendar table and Employeemaster table

Calendar table  date col with target table date col as well employee master table employee id col with target table employee id col

[Thejeswar]

Hi @Charu,

It is quite difficult to decide on the relationship between the table without knowing the actual business need.

Also without having understanding on how your current relationship between the tables are, it might not be a good idea to suggest a relationship.

However, from your explanation I get that there are 3 tables.

1. Calendar

2. Employee master

3. Target

Your current relationships are

1. Calendar joined with Target by date

2. Employee master with target based on Employee ID

Now you need to join calendar and employee master.

So your model should potentially look like the one below

Model with inactive Relationship

As you can see in the above screenshot a loop is formed when this connection is made.

The Dotted line in the above image shows that the relationship between Calendar and Employee Master is inactive. The Below image shows that when I try to  make the relationship active, it gives the same error that you got.

When the relationship is made active, gives ambiguity error

But something to note here is, since your 3 tables are indirectly related, (your employee master and calendar table are related using target table), that data should be fine even without establishing a connection between calendar and employee master as shown in the below image

Result Set 

Did you try verifying the data in the report built using these tables? 

I would suggest that try to build the scenario without having join between calendar and employee master, to verify if the join between those tables is still mandatory.

Also if this does not solve your requirement, then a look at your datamodel might be required ( if possible)

In case if that is sensitive, build a dummy data model with a similar structure and share it or get in touch with someone who has knowledge on your data as well as data modelling

Hope this helps!!!

Regards,

Thejeswar