DAX or Query: Compare two columns in different Tables

[Anonymous]

‎Hi @PowerBI 

I have 2 tables and want to compare two columns (one from each) in query editor or dax. I just need a yes they (the text values) match or no (text values) don't match in a new column. Secondly I need a count of all the orders that do not match to table 2.

 Table 1 (Primary Table)

Order No	Order Status
1234	Resolved
1235	Pending
1236	Open
1237	Resolved
1238	Resolved
1239	Pending

Table 2

Order No	Order Status
1234	Open
1235	Resolved
1236	Pending
1237	Resolved
1238	Resolved
1239	Open

I want a new column to be created (in either table) that shows when the Order status values in rows from each table match, as below:-

Order No	Order Status	Column 3
1234	Resolved	No Match
1235	Pending	No Match
1236	Open	Match
1237	Resolved	Match
1238	Resolved	Match
1239	Pending	No Match

Do i need to do this as a merge query or dax ?

Thanks

[Anonymous]

I have 2 tables and want to compare two columns (one from each) in query editor or dax. I just need a yes they (the text values) match or no (text values) don't match in a new column. Secondly I need a count of all the orders that do not match to table 2.

 Table 1 (Primary Table)

Order No	Order Status
1234	Resolved
1235	Pending
1236	Open
1237	Resolved
1238	Resolved
1239	Pending

Table 2

Order No	Order Status
1234	Open
1235	Resolved
1236	Pending
1237	Resolved
1238	Resolved
1239	Open

I want a new column to be created (in either table) that shows when the Order status values in rows from each table match, as below:-

Order No	Order Status	Column 3
1234	Resolved	No Match
1235	Pending	No Match
1236	Open	Match
1237	Resolved	Match
1238	Resolved	Match
1239	Pending	No Match

Do i need to do this as a merge query or dax ?

Thanks

[aj1973 · Accepted solution]

Hi @Anonymous 

here you go

Attached the file

https://drive.google.com/file/d/1hMWpl1vwwXlj8yDKT8vmXLUF7S207jUU/view?usp=sharing

[Anonymous]

@aj1973 Thank you so much this worked! I need to do a count of the total orders that have mismatched in the 3rd table how do I do this?

[aj1973]

I just updated the file for you

[Anonymous]

Hi @aj1973 unfortunatelty this didn't work 😞 

The count is just giving me the count of all the rows in the 1st table not the new table created. 

Also I have a change in requirement I need the 3rd table to show the order no, order status from both table 1 and 2, and  new column to be created that shows when the Order status values in rows from each table match, as below:-

Order No	Order Status (T1)	Order Status (T2)	Column 3
1234	Open	Resolved	No Match
1235	Resolved	Pending	No Match
1236	Pending	Open	Match
1237	Resolved	Resolved	Match
1238	Resolved	Resolved	Match
1239	Open	Pending	No Match

I am getting duplicate rows when I take both table columns however can see the match/no match values. whereas if I do your example way it shows me all values as no match

[aj1973]

@Anonymous 

How it is not working? what did you change? Can you send me the same file that is not showing the correct result?

I just updated the file using different way (Added all columns to Table 1). Check it out.

[Anonymous]

link

[aj1973]

Here is your file corrected

I changed the way of calculation: I added 2 columns to OEC_ Table:

I renamed the "No need table" and "No need measure" delete them if you want.

Hope you like it😁

[Anonymous]

@aj1973 thank you! There is an issue - the value are not suppose to be empty in the ESR status when there is no match it should have a value.

I'm not sure if its because I determined a 1:1 relationship.

[aj1973 · Accepted solution]

Hey add this to the measure if you don<t want to count the Blank rows

Count of MisMatch = CALCULATE(COUNTROWS(OEC_Service_Requests) , OEC_Service_Requests[Match/No Match__] = "No Match", OEC_Service_Requests[Status_Code ESR] <> BLANK())

[Anonymous]

Thank you !! @aj1973 this has worked!! 🙂

[aj1973]

Good

one last thing to know where the Blank is coming from

those numbers don't existe in the other table

[Anonymous]

@aj1973  Hi Amine,

I have a new table which is the different date range of orders (table 1) How do I move all the calculations to connect to the new table instead of the exisiting table 1?

I don't want to redo all the calculations.

Thanks

[aj1973]

@Anonymous sorry but i am kinda lost here. what's the issue? I thought it was solved Ain't it?

[aj1973]

Indeed, 1:1 relationship is not recommended 

Better way is to add a Calendar Table and

but you are going to need to change some calculations.

another way is to filter out the Blank rows from the filter pane.

[aj1973 · Accepted solution]

Hi @Anonymous 

here you go

Attached the file

https://drive.google.com/file/d/1hMWpl1vwwXlj8yDKT8vmXLUF7S207jUU/view?usp=sharing

[Rookzie]

Hello, 

i tired to enter this in as a new coulum but all i got back was "match" when i know some dont match. 

any reason why this would happen? 

[aj1973]

Hi @Rookzie 

I don't understand what are you talking about !!!

Can you open a new thread with more details of your issue and share it with me please?

[Anonymous]

Hi @aj1973 I had the same issue as Rookzie, looks like the formula worked with no errors but the but all the results are a "match" ,

Looking at this again I see that my tables don't have a common column, the common column, when they interact the common column or the intersection is in another table. Do you know how I can do this?   

could you advise why this might be? thank you!

[aj1973]

Hi @Anonymous 

Thank you for contacting me but this thread is more that a year old, there is always new things in Power Bi that I can help you with in your case. Besides every model is unqiue therefore can you share yours so I can be more helpfull?

As I replied to Rookzie, he needed to open a new thread however from what you have described maybe adding an external Excel file with a "Commun Column" could help you out!

So please Open a new thread and share your Model OR text me in Private if it would suits you better.

[Anonymous]

I have opened a new thread, thank you so much!

[tulasi_pbi1988]

Hi @Anonymous 

If the relationship is Many to Many means?

I have the same scenario to do, but I have Many to Many relationship.

Do you have anything like this, please?

Thank You