topic Re: Connecting 2 Tables in DAX Commands and Tips

Connecting 2 Tables

Asking — Fri, 14 Oct 2022 15:34:26 GMT

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

Table 2

1 j

1 k

2 h

4 j

Result:

1 Y

2 Y

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

2 N

I hope I am explaining myself correctly.

Appreciate any help

Re: Connecting 2 Tables

amitchandak — Sat, 15 Oct 2022 02:06:59 GMT

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")

Re: Connecting 2 Tables

v-yueyunzh-msft — Mon, 17 Oct 2022 02:38:01 GMT

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