Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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
Solved! Go to Solution.
Hi @Anonymous
here you go
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
Hey add this to the measure if you don<t want to count the Blank rows
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 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?
I just updated the file for you
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
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
@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
link
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
@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.
Hey add this to the measure if you don<t want to count the Blank rows
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
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
@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
@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
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
Hi @Anonymous
here you go
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?
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
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!
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
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
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
93 | |
88 | |
83 | |
76 | |
49 |
User | Count |
---|---|
145 | |
140 | |
109 | |
68 | |
55 |