cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not applicable

## Finding out if numeric values of one column are present in another numeric column of another table

I have two tables, and both have a column "work item id". I need to find out how many of the id´s of column from table 1, are also present in table 2 (specifically to find which are absent), and I need this result in a calculated column.

Table 1 has around 500 numeric entries in the column "work item id", while table 2 has about 460 numeric entries in the column "work item id". I need a calculated column to find out which entries are not present in "work item id" table 2, but are present in "work item id" table 1.

1 ACCEPTED SOLUTION
Super User

@Anonymous , A new column in table 1

new column =

var _cnt = countx(filter(Table2, Table2[ID]= Table1[ID]), Table2[ID])

return

if(isblank(_cnt), 0,1 )

4 REPLIES 4
Solution Supplier

Hi,
I think this should be a way to solve it:
Matching_Column =
var x_1 = SELECTEDVALUE('Table 1'[work item id])

var x_2 = SELECTEDVALUE('Table 2'[work item id])

return

IF(x_1 = x_2, "Present","Not Present")

I hope that helps 🙂

Anonymous
Not applicable

No, every value comes as "Present".

Super User

@Anonymous , A new column in table 1

new column =

var _cnt = countx(filter(Table2, Table2[ID]= Table1[ID]), Table2[ID])

return

if(isblank(_cnt), 0,1 )

Anonymous
Not applicable

Thank you so much!! Worked like a charm! Can you (in short) explain the code you wrote? As I am new to this, I would learn that way. SO many Thanks

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors