Compliance check of 2 tables

[vnqt]

Hi,

I have 2 tables A and B without direct relation.

 Table A there are 3000 Names and 2000 names in Table B 

I would like to compare the name of 2 tables to check the compliance of these 2 tables :

- number of same names (Card view)

- number of different names (Card view)

and a list (table view) of these same and different names. 

Could you please advise? 

Thank you in advance.

Tg

[Anonymous · Accepted solution]

Hi @vnqt ,

I suggest you to create a DimName table with all distinct names and then do calculation based on this table.

DimName = 
VAR _UNION = UNION(VALUES('Table A'[Name]),VALUES('Table B'[Name]))
RETURN
SUMMARIZE(_UNION,[Name])

My Sample is as below.

Table A:

Table B:

Measure:

number of same names =
CALCULATE (
    COUNT ( DimName[Name] ),
    FILTER (
        DimName,
        AND (
            DimName[Name] IN VALUES ( 'Table A'[Name] ),
            DimName[Name] IN VALUES ( 'Table B'[Name] )
        )
    )
)

number of different names = CALCULATE(COUNT(DimName[Name])) - [number of same names]

Filter Same list =
IF (
    AND (
        SELECTEDVALUE ( DimName[Name] ) IN VALUES ( 'Table A'[Name] ),
        SELECTEDVALUE ( DimName[Name] ) IN VALUES ( 'Table B'[Name] )
    ),
    1,
    0
)

Result is as below.

Add [Filter Same list] into Same Name list (visual level filter) and set it to show items when value = 1.

Add [Filter Same list] into Different Name list (visual level filter) and set it to show items when value = 2.

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.

[djurecicK2]

Hi @vnqt ,

 You can use the LOOKUPVALUE function to compare fields from tables that are not related.

https://learn.microsoft.com/en-us/dax/lookupvalue-function-dax