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.
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?
Solved! Go to 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
@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
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 -
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
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |