Forum Discussion
Connecting 2 Tables
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
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
2 Replies
- amitchandak
Super User
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")
- v-yueyunzh-msft
Community Support
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
