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.
Hi all,
I am facing an issue in which I would like to compare if the values in two columns match in two tables. When in the other table the same combination exists, I would like to have an outcome of 'Yes' (or 'True'). I would like to make an overview of the percentage that is matching in the end, so combining tables could give false insights.
Here I have an example:
Table 1
Column 1 | Column 2 | Result column |
Mechanical | Item1 | Yes |
Mechanical | Item2 | Yes |
Mechanical | Item54 | No |
Electronics | Item2 | No |
Electronics | Item3 | Yes |
Table 2
Column X | Column Y |
Mechanical | Item1 |
Mechanical | Item2 |
Mechanical | Item56 |
Electronics | Item3 |
Electronics | Item4 |
What I did until now was the following:
Match=
IF (
'Table 1' [Column 1] IN DISTINCT ( 'Table 2'[Column X] ),
IF (
'Table 1' [Column 2] IN DISTINCT ( 'Table 2'[Column Y] ),
"Yes",
"No"
),
"No"
)
At first sight it seems to be working, however, some values are false positive; it happens to see matches that are not there. What could be the problem?
Solved! Go to Solution.
@ddorhout , new column in table 1
New column =
var _1 = countx(filter(Table2, Table1[Column 1] = Table2[Column X] && Table1[Column 2] = Table2[Column Y] ) Table2[Column X])
return
if(isblank(_1, "No", "Yes")
@ddorhout , new column in table 1
New column =
var _1 = countx(filter(Table2, Table1[Column 1] = Table2[Column X] && Table1[Column 2] = Table2[Column Y] ) Table2[Column X])
return
if(isblank(_1, "No", "Yes")
Hi Amitchandak Thanks for your reply!
Great!
One addition to made, then it worked:
if(isblank(_1), "No", "Yes")
Hey @ddorhout ,
I would summarize by these 2 columns and then do an intersection.
Check if the following approach works:
Matching Measure =
VAR vIntersectionTable =
INTERSECT (
SUMMARIZE ( 'Table 1', 'Table 1'[Column 1], 'Table 1'[Column 2] ),
SUMMARIZE ( 'Table 2', 'Table 2'[Column X], 'Table 2'[Column Y] )
)
RETURN
IF( COUNTROWS( vIntersectionTable ) > 0, "Yes", "No" )
Until now it gives only positive values with the code... You have an idea of what could be going wrong?
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 |
---|---|
114 | |
100 | |
78 | |
75 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |