Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
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
@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
ya actually you can use USERELATIONSHIP() in the measures wherever there is not a direct active relationship between tables
Regards,
Thejeswar
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?
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
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
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
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.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |