Count common values between tables that are not related

[Syndicate_Admin]

Nice day

I ask for your help, I need to create a DAX expression to count the common values between two tables that are not related. In both tables what I have in common are the identification numbers of the people, however, they are multiple records that are repeated in both tables.

I thank you for the help you can give me.

[Syndicate_Admin]

Thank you very much for the help, however, I need the DAX formula to apply it for example on a card to show me the result of the count, of the values that in this case would have the two tables in common in the ID field

[v-rzhou-msft · Accepted solution]

Hi @Syndicate_Admin ,

I suggest you to try code as below.

VAR _LIST =
    CALCULATETABLE (
        VALUES ( Table2[Record] ),
        FILTER ( Table2, Table2[ID] = MAX ( Table1[ID] ) )
    )
RETURN
    CALCULATE ( COUNTROWS ( Table1 ), FILTER ( Table1, Table1[Record] IN _LIST ) )

Best Regards,
Rico Zhou

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

[gmsamborn]

Maybe try this.

Count = 
    SUMX(
        'Table1',
        [Measure]
    )

[v-rzhou-msft]

Hi @Syndicate_Admin ,

Here I create a sample to have a test.

Table1:

Table2:

Measure:

Measure = 
VAR _LIST = CALCULATETABLE(VALUES(Table2[Record]),FILTER(Table2,Table2[ID] = MAX(Table1[ID])))
RETURN
IF(MAX(Table1[Record]) IN _LIST,1,0)

Add this measure into visual level filter and set it to show items when value = 1.

Best Regards,
Rico Zhou

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

[lbendlin]

Read about SELECTCOLUMNS, VALUES and INTERSECT

SELECTCOLUMNS - make sure the columns have the same name

VALUES - get unique values

INTERSECT - find commonality