Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello community.
I am once again stump as to how I could approach this issue. I need to identify and display where data is invalid please see example below.
Table1:
ID | Value |
1 | A |
2 | B |
3 | C |
4 | D |
Table2:
ID | Value |
1 | A |
1 | A |
2 | B |
2 | B |
4 | D |
4 | D |
Desired outcome:
ID | Value |
1 | A |
1 | A |
2 | B |
2 | B |
3 | No value in table 2. Only in table 1. |
4 | D |
4 | D |
I know the obivous solution would be to merge two tables and create a calculated column and evaluate if column Value is blank put "No value in table 2. Only in table 1." else return Value.
Nature of my dataset large so this is not feasible as it would take too long to refresh. Is there any other way to do this with a one to many cardinality relationship? So far I am using the show item with no data and it is showing the ID correctly. But I am not sure how to not show the blank but rather flag it as an issue to report to my manager that people are entering data incorrectly.
Hi @Anonymous
Create a new Table with the following DAX expression:
Table =
VAR _A =
SUMMARIZE (
EXCEPT ( 'Table 1', 'Table 2' ),
[ID],
"Value", "No value in table 2. Only in table 1."
)
VAR _B =
UNION ( 'Table 2', _A )
RETURN
_B
Output:
Sample file attached, please download.
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
Hi,
just tried this, i get error that my table doesnt have the same number of column. Is there any other options?
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |