Compare data from two different table visuals that exist from different data sources

[blackbach2728]

I have two table visuals that I have created in a dashboard in two seperate pages. This data is from two different data sources that I connected to by direct query. I need a count of how many records match from Table A to Table B by comparing the 'RelaySubstationID', 'RelayCircuitID', 'ProtectionType', 'Setting', and 'Value' columns. The 'Value' column consists of alphanumeric values but I want to compare only the numeric part. In the below case, I'd expect to get a count of 4 matching records. Is there any way to do this or is it outright impossible?

Table ATable B

[Anonymous · Accepted solution]

Hi @blackbach2728 

Please try this:
Here are 2 sample data:

Table A:

Table B:

Click Transform data in the Home pane to use power query.

Click Custom Column in the Add Column pane:

You can use this m-code to get numbers from columns:

Text.Select([Value],{"0".."9","."})

Add for both 2 tables:

Close&Apply.

Finally, add a measure:

MEASURE =
COUNTROWS (
    FILTER (
        CROSSJOIN ( 'Table A', 'Table B' ),
        'Table A'[Custom] = 'Table B'[Custom]
    )
)

The result is as follow:

Best Regards

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

[parry2k]

@blackbach2728 it is possible but before you get there you need to create dimensions table of all the common column that you want to compare against. Each dimension table will link to each data table and then you can compare the value column from both the tables. 

This is just an idea, you can tweak the solution as it fits for you.

To compare only number value from value  column you can do something like this:

Compare Number Values = 
VAR __ValueTableA = IFERROR ( VALUE ( SELECTEDVALUE ( 'TableA'[Value] ) ), BLANK () )
VAR __ValueTableB = IFERROR ( VALUE ( SELECTEDVALUE ( 'TableB'[Value] ) ), BLANK () )
RETURN
SWITCH ( 
   TRUE (),
   __ValueTableA == BLANK () && __ValueTableB == BLANK (), "Non number value both A and B",
   __ValueTableA == BLANK (), "Non number value table A",
   __ValueTableB == BLANK (), "Non number value table B",
   __ValueTableA <> __ValueTableB, "Mistmatched numbers",
  "Value Matched"
)

[danextian]

Hi @blackbach2728 

You can do an intersect between the two columns from tables A and B

COUNTROWS ( INTERSECT ( VALUES ( table1[value] ), VALUES ( table2[value] ) ) )

But both columns must contain numbers only. While that can be done in Power Query, that will most likely break query folding as welll, breaking direct query.