Comparing 2 columns for matches

[bjanzen]

Hello Everyone,

       I have been trying to solve for a problem to see what is possible.

I have two tables that contain subnets. 

 Table 1

Subnets

10.0.50.0

10.0.60.0

10.0.70.0

Table 2

10.0.50.0

10.0.70.0

10.0.80.0

I would like to compaire the 2 tables to have a pie char to say something like true/false depending on if there are matches.

I am a little lost as to what command would allow that type would something like ISERROR  work for this?

thanks for any pointers!

Brett

[v-yuta-msft · Accepted solution]

Hi bjanzen,

Based on your description, you want to compare column Subnets in two tables row by row, right?

To achieve your requirement, please follow steps below:

1.Create index column in two tables, click Edit Queries->Add Column->Index Column.

2.Create relationship between two tables based on index column, click Manage Relationships->New->Select index column in table1->Select index column in table2->One to One and Both direction->Make this relationship Active->Apply security filter in both directions.

 

3.After a relationship has been built, you can create a measure two compare two columns of different table:

Compare = IF(Table1[Subnets] = RELATED(Table2[Subnets]), 0, 1)

4.Then calculate the number of matched Subnets and unmatched Subnets using DAX formula below:

Number Of True = CALCULATE(COUNT(Table1[Index]), FILTER(Table1, Table1[Compare] = 0))
Number Of False = CALCULATE(COUNT(Table1[Index]), FILTER(Table1, Table1[Compare] = 1))

5.In final, create a pie chart to compare number of Subnets in two table like below:

You can refer to the sample PBIX file here: https://www.dropbox.com/s/3rzo88hjpi7lx5t/For%20bjanzen.pbix?dl=0

Best Regards,

Jimmy Tao

[bjanzen]

That is exactly what I am trying to do, thanks so much for this. 

The more and more I use Power BI the more potential I can see this tool having.

thanks @v-yuta-msft!
Brett