Connecting 2 Tables

[Asking]

Hi,

I have 1 table with ID # column which are not unique. I have another table with names and ID# which are not unique. I want to add a column to table 1 with a flag showing if the ID exists in table 2. The challege is that I wish to filter both tables in the dashboard. Meaning:

Table 1

1

1

2

3

3

4

Table 2

1 j

1 k

2 h

4 j

Result:

1 Y

1 Y

2 Y

3 N

3 N

4 Y

However, if I use the slicer in the dashboard and choose only j or k or both from table 2 and only ID # 1 and 2 from Table 1, the result should be:

1 Y

1 Y

2 N

I hope I am explaining myself correctly.

Appreciate any help

[v-yueyunzh-msft · Accepted solution]

Hi, @Asking 

For your needs, the values change dynamically according to the slicer, and the calculation cannot be dynamically calculated in the visual, so we need to use the new measure to achieve your needs.

Here are the steps you can refer to :
(1)To automatically deduplicate the Table and Matrix visuals, we need to create a new Index column in Table1:

(2)We need to create a measure :

Measure = 
var _id = VALUES(Table1[ID])
var _t_count =COUNTROWS( FILTER( 'Table2' ,  'Table2'[ID] in _id))
return 
IF( _t_count =BLANK() ,"N", "Y")

(3)Then we put the filed in the visual , and we will meet your need :

(4)We can also close the "Column headers-Text wrap" to hide the 'index' column:

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak]

@Asking , You need a measure in this case

measure  =

var _tab = values(Table2[ID])

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

return

if(isblank(_tab) , "N" else "Y")

Or add new column in table 1 and join two tables and try filter

New column =

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

return

if(Isblank(_cnt) , "N", "Y")