Forum Discussion

Asking's avatar
Asking
Regular Visitor
3 years ago
Solved

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 t...
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    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

     

     

v-yueyunzh-msft's avatar
v-yueyunzh-msft
Icon for Community Support rankCommunity Support
3 years ago

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