Conditional join on multiple columns

[Anonymous]

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?

[Anonymous]

@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]

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:

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?

[Anonymous · Accepted solution]

@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