Slicer from table 1 applies to table 2 (same page) - Check and compare columns in different tables

[ljr91-us]

I have following two tables:

In current visual, I have the [year_month] from table 1 as a slicer. I want to create a new flag column in table 2 to check if [Unit] from Table 2 exists in Table 1.

Raw Data:

My problems is I can get the flag column created in Table 2, but the slicer from Table 1 does not apply to table 2, even they are in the same page/view.  For example, if I select year_month slicer to "2022-12", my current output still has unit 7 and 8 flag as YES, they should be NO because slicer '2022-12' filter out records (unit 7, 8 ) from table 1.

my current code (add new column):   flag= IF ( Table2[unit] IN DISTINCT ( Table1[unit]), "YES", "NO" )

I removed all relationship between table 1 and table 2, because I want to display all values 1-10 in table 2, can anyone let me know how to upload the PBIX file?

[amitchandak]

@ljr91-us , new column in table 2

new col = var_cnt = countx(filter(Table1, Table2[Unit] =Table1[Unit]) ,Table1[Unit])

return

if(isblank(_cnt), "no", "Yes")

refer 4 ways (related, relatedtable, lookupvalue, sumx/minx/maxx with filter) to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

[ljr91-us]

Hi,

It is the same as my current output, which is not the desired output.  

I want to have

1) table 2 to display all 1-10 unit 

2) having slicer filter out table 1 records, then use filtered records to compare with table 2

[JosephWu · Accepted solution]

Hi, @ljr91-us 
I create a measure (reference from amitchandak Reply)  

And there is a relationship with Table1[unit] and Table2[unit]

Measure = 
var cnt = countx(filter(Table1, SELECTEDVALUE(Table2[Unit]) = Table1[Unit]) ,Table1[Unit])

return

if(isblank(cnt), "no", "Yes")

 Result :

Best regards,
Joseph