Forum Discussion
Comparing 2 columns for matches
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
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
2 Replies
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
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
