Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

DAX or Query: Compare two columns in different Tables

 

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 NoOrder Status
1234Resolved
1235Pending
1236Open
1237Resolved
1238Resolved
1239Pending

 

Table 2

Order NoOrder Status
1234Open
1235Resolved
1236

Pending

1237Resolved
1238Resolved
1239Open

 

 

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 NoOrder StatusColumn 3
1234ResolvedNo Match
1235PendingNo Match
1236OpenMatch
1237ResolvedMatch
1238ResolvedMatch
1239PendingNo Match

 

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

 

Thanks

2 ACCEPTED SOLUTIONS
aj1973
Community Champion
Community Champion

Hi @Anonymous 

here you go

aj1973_0-1625145142041.png

 

Attached the file

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

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

View solution in original post

aj1973
Community Champion
Community Champion

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())

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

View solution in original post

22 REPLIES 22
Anonymous
Not applicable

@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
Community Champion
Community Champion

I just updated the file for you

aj1973_0-1625160485973.png

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Anonymous
Not applicable

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 NoOrder Status (T1)

Order Status

(T2)

Column 3
1234OpenResolvedNo Match
1235ResolvedPendingNo Match
1236

Pending

OpenMatch
1237ResolvedResolvedMatch
1238ResolvedResolvedMatch
1239OpenPendingNo 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

 

nxliii_1-1625220270237.png

 

 

 

 
aj1973
Community Champion
Community Champion

@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.

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Anonymous
Not applicable

link

aj1973
Community Champion
Community Champion

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😁

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Anonymous
Not applicable

@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
Community Champion
Community Champion

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())

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Anonymous
Not applicable

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

aj1973
Community Champion
Community Champion

Good

one last thing to know where the Blank is coming from

 

those numbers don't existe in the other table

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Anonymous
Not applicable


@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
Community Champion
Community Champion

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

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

aj1973
Community Champion
Community Champion

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.

 

 

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

aj1973
Community Champion
Community Champion

Hi @Anonymous 

here you go

aj1973_0-1625145142041.png

 

Attached the file

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

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

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
Community Champion
Community Champion

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?

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Anonymous
Not applicable

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
Community Champion
Community Champion

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.

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Anonymous
Not applicable

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

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.