March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hello,
I want to check one items is exist or not from one table to another table and display in 3rd Table as mentioned at the end of mesage
Above Table is master table, this table different set of level and level 2 and also mention which levels are mandatory and for which region.
Below table is Machinery table. This table having various machinery related items, which belong to different region with different type of levels like level 1 and level.
I want to get the machinecode where Mandatory level1/level2 are missing to machine specific region to check from master table to machine table. I want result should be
I want to create a column or measure, Please help.
Regards
Avian
Solved! Go to Solution.
Please try this ...
Click here to download a solution
How it works:-
I used Power Query to transform your tabels to a star schema
You can learn how by looking at the queries ...
I then created 2 dax measure
Thanks for reaching out for help.
I put in a lot of effort to help you, now please quickly help me by giving kudos.
Remember we are unpaid volunteers and here to coach you with Power BI and DAX skills and techniques, not do the users job for them. So please click the thumbs up and accept as solution button.
If you give someone a fish then you only give them one meal, but if you teach them how to fish then they can feed themselves and teach others for a lifetime. I prefer to teach members on this forum techniques rather give full solutions and do their job. You can then adapt the technique for your solution, learn some DAX skills for next time and soon become a Power BI Super User like me.
One question per ticket please. If you need to extend your request then please raise a new ticket.
You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you !
Please try this ...
Click here to download a solution
How it works:-
I used Power Query to transform your tabels to a star schema
You can learn how by looking at the queries ...
I then created 2 dax measure
Thanks for reaching out for help.
I put in a lot of effort to help you, now please quickly help me by giving kudos.
Remember we are unpaid volunteers and here to coach you with Power BI and DAX skills and techniques, not do the users job for them. So please click the thumbs up and accept as solution button.
If you give someone a fish then you only give them one meal, but if you teach them how to fish then they can feed themselves and teach others for a lifetime. I prefer to teach members on this forum techniques rather give full solutions and do their job. You can then adapt the technique for your solution, learn some DAX skills for next time and soon become a Power BI Super User like me.
One question per ticket please. If you need to extend your request then please raise a new ticket.
You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you !
Thanks for the solution. Is there anyway to use SQL query like
SELECT Level2
FROM table1
WHERE NOT EXISTS
(SELECT *
FROM table1
WHERE table1.Level2 = table2.Level2)
This is the Power BI forum not the SQL forum.
Please can you click thumbs up and accept solution. I spent a long time on the solution and it works. Thank you.
I already did to your original solution and thanks for your support.
Thank you. It was a fun Power Query problem.
Hope you learned some new skills from the solution.
Please can you explain the last rows on Table 3
Why it is twice?
and how do you derive the values please?
MC00004 | APAC |
Mechanical drawings |
Please dont show example data as screen shots.
Pleasee copy and paste it as table text then we can import the data and build a solution. Thank you. 😀
Here are tables, Hope this helps you. FIrst 2 tables I am generating from SharePoint Lists
Table1
Level1 | Level2 | Mandatory | Region |
User Manual | Machine Instruction manual | Yes | NAFTA |
Mechanical drawings | Mechanical drawings | Yes | APAC |
Mechanical drawings | Fixtures schematics | No | |
Mechanical drawings | Lay-out design | No | |
Electrical schema | Electrical scheme design | Yes | EMEA |
Electrical schema | Electrical panels Layout drawings | No | |
Electrical schema | Main control system documentation | No | |
Machine Schema | Machine Schema Design | Yes | EMEA |
Safety documentation | Certification issued by Engineer officially | Yes | EMEA |
Safety documentation | Risk assessment documentation | Yes | APAC |
Safety documentation | Certifications of systems | No | |
Safety documentation | Disaster Recovery procedure | Yes | NAFTA |
Maintenance | Preventive Maintenance Manual | No | |
Maintenance | Maintenance manual | No |
Table 2
Machine Code | Plant Region | Level1 | Level2 |
MC00001 | NAFTA | Mechanical drawings | Fixtures schematics |
MC00001 | NAFTA | Mechanical drawings | Lay-out design |
MC00001 | NAFTA | Maintenance | Maintenance manual |
MC00002 | EMEA | Electrical schema | Electrical scheme design |
MC00002 | EMEA | Mechanical drawings | Fixtures schematics |
MC00003 | EMEA | Electrical schema | Main control system documentation |
MC00004 | APAC | Maintenance | Maintenance manual |
MC00004 | APAC | Maintenance | Preventive Maintenance Manual |
MC00004 | APC | Electrical schema | Electrical panels Layout drawings |
Table 3
Machine Code | Region | Mising Level 2 |
MC00001 | NAFTA | Machine Instruction manual |
MC00001 | NAFTA | Disaster Recovery procedure |
MC00002 | EMEA | Machine Schema Design |
MC00002 | EMEA | Certification issued by Engineer officially |
MC00003 | EMEA | Electrical scheme design |
MC00003 | EMEA | Machine Schema Design |
MC00003 | EMEA | Certification issued by Engineer officially |
MC00004 | APAC | Mechanical drawings |
MC00004 | APAC | Mechanical drawings |
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
94 | |
86 | |
84 | |
76 | |
49 |
User | Count |
---|---|
160 | |
144 | |
103 | |
74 | |
57 |