cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Conditional join on multiple columns

I am creating a data quality report in Power BI. I have a table "data_errors" that contains data errors which I have extracted from multiple tables. I want to create one report that shows all the data errors from multiple tables, but therefore I need to create a distinction between the supplier table and the project table.

 

This table data_errors contains the following columns:

table_name,

column_name,

skey,

value

 

With the skey column I can make a relationship to the other tables by using the skey from the analysed table. I need to find a way to make a relationship between the data_errors table and the analysed tables, to visualize the values that are wrong.

 

For example:

I have two different tables which I have analysed: "supplier" and "project". Both tables have a unique value: supplier_skey and project_skey. To make a relationship to my data_error table, I need to check if the table_name and table_column matches. If the table and column matches, I need to create a relationship with the skeys from the data_error table and the supplier or project table.

 

My data_error table looks like this:

table_name	               column_name                skey	                 value
supplier	               order_type	          105967	         99/
supplier	               payment_condition          10002	                 10/blocked
supplier	               name	                  35828	                 ??????
project project_number 46428 699710
project project_type 17275 -

My "supplier" table looks like this:

supplier_skey       order type         payment_condition       name       
105967              email              30 days                 xxx
35828               email              60 days                 yyy
10002               email              15 days                 zzz

My "project" table looks just like my supplier table:

project_skey       project_type         project_number        name       
46428              renovation           00001                 xxx
17275              utility              00002                 yyy

Is it possible to join the tables IF data_error.table_name = supplier AND

                                                     data_error.column_name = supplier.column THEN

                                                     data_error.skey = supplier.supplier_skey

 

How can I join the tables with each other?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User II
Super User II

Re: Conditional join on multiple columns

@Anonymous  - 

Ah, this problem occurs when you have multiple "paths" between 2 tables. If 2 active relationships were allowed, the formula engine would be unable to determine how to filter.

There are two options:

1. Avoid the ambiguity by creating a copy of one of the tables and join the new table to the error table.

2. Make a relationship Inactive. This means that filtering is not automatically applied between the table and the error table. In order to apply filtering you will need to use the DAX function USERELATIONSHIP.

Hope this helps,

Nathan

View solution in original post

3 REPLIES 3
Super User II
Super User II

Re: Conditional join on multiple columns

@Anonymous  - 

You need to create relationships manually.

One way to accomplish your goal is to create a new column towhich concatenates the table name and the key in each of the tables. Then create a relationship on this new column from the error table to each table.

Hope this helps,

Nathan

Anonymous
Not applicable

Re: Conditional join on multiple columns

I have tried your solution, but I receive an error when I try to make a relationship between both project and supplier from the data error table. Creating a relationship with one of the two tables in no problem. When I try to create a relationship with the second table I get the following error:ambiguity ralationship powerbi.PNG

Making the relationship go single or both ways does not make a difference.

 

I have created custom columns in project and supplier as followed:

Project table:        project_dataerror_skey = tableName + project_skey

Supplier table:      supplier_dataerror_skey = tableName + supplier_skey

data_error table:   data_error_skey = tableName + skey

 

The skey's from supplier and project can occur more than once, because one supplier or project can have more than one data errors. The project and supplier table has the same skey as shown below:

 

dataerror_skey
supplier-105967
project-17275
supplier-105967
supplier-10002

Is there a quick fix to make the relationship between the tables?

 

Super User II
Super User II

Re: Conditional join on multiple columns

@Anonymous  - 

Ah, this problem occurs when you have multiple "paths" between 2 tables. If 2 active relationships were allowed, the formula engine would be unable to determine how to filter.

There are two options:

1. Avoid the ambiguity by creating a copy of one of the tables and join the new table to the error table.

2. Make a relationship Inactive. This means that filtering is not automatically applied between the table and the error table. In order to apply filtering you will need to use the DAX function USERELATIONSHIP.

Hope this helps,

Nathan

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors