I have a data model as shown below where I have established relationship between two tables from two columns named orderid and itemid, but only orderid column is active
Can I establish active relationship for both the columns, any ways of doing it?
1 ACCEPTED SOLUTION
You can join the tables in the Query, depending on the context of your data you would need to pick the appropriate join (Left, Inner, Full Outer). These are basic SQL join parameters. Website for details here:
https://www.w3schools.com/sql/sql_join.asp
If you want to create this relationship you have 3 options:
1) Joins [Left, Inner, Full Outer] in Power Query
2) Summary tables to make distinct keys for relationships to tables
3) DAX - USERELATIONSHIP function that was mentioned above.
Hi! For context, why does there need to be a relationship from:
ItemID [Order_Items] to ItemID [Orders]
OrderID [Order_Items] to OrderID [Orders] ?
All you need is a single relationship to connect the tables, however, a better way to structure this may be either creating 2 s_tables (each with a distinct primary key of OrderID and ItemID) or if there is a primary/foreign key relationship you can make one s_table of primarykey,foreignkey.
Distinct List of Order ID:
Union OrderID from both tables, take distinct. This can be done in query or DAX.
@Anonymous orderid and itemid are just examples i have given, i just need to know whether realtionship can be established from multiple columns that acts like AND operator
Understood, @negi007 answers this question then. Another option is to join the tables together or create a unique table with the primary/foreign key relationship and link that to both of the related tables. PowerBI does not allow for multiple relationships between the same tables.
@sheetals you can have only one active relationship between two tables but you can use inactive relationship in your dax using
USERELATIONSHIP(<columnName1>,<columnName2>)
USERELATIONSHIP
USERELATIONSHIP(<columnName1>,<columnName2>)
Parameters
| Term | Definition |
|---|---|
| columnName1 | The name of an existing column, using standard DAX syntax and fully qualified, that usually represents the many side of the relationship to be used; if the arguments are given in reverse order the function will swap them before using them. This argument cannot be an expression. |
| columnName2 | The name of an existing column, using standard DAX syntax and fully qualified, that usually represents the one side or lookup side of the relationship to be used; if the arguments are given in reverse order the function will swap them before using them. This argument cannot be an expression. |
https://docs.microsoft.com/en-us/dax/userelationship-function-dax
You can join the tables in the Query, depending on the context of your data you would need to pick the appropriate join (Left, Inner, Full Outer). These are basic SQL join parameters. Website for details here:
https://www.w3schools.com/sql/sql_join.asp
If you want to create this relationship you have 3 options:
1) Joins [Left, Inner, Full Outer] in Power Query
2) Summary tables to make distinct keys for relationships to tables
3) DAX - USERELATIONSHIP function that was mentioned above.
