topic Re: Compliance check of 2 tables in DAX Commands and Tips

Compliance check of 2 tables

Anonymous — Tue, 29 Nov 2022 14:30:56 GMT

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.

Re: Compliance check of 2 tables

djurecic — Tue, 29 Nov 2022 14:47:19 GMT

Hi Anonymous ,

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

Re: Compliance check of 2 tables

Anonymous — Wed, 30 Nov 2022 08:36:17 GMT

Hi Anonymous ,

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.