Measure to check one item is exist or not from one table to another table

[Avian65]

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

[speedramps · Accepted 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

Has actuals = INT(NOT(ISEMPTY('Fact actuals')))

Has targets = INT(NOT(ISEMPTY('Fact targets')))

These simple return 1 if there is any data in the filter context, otherwise they return 0.

Then I used these measure as filters in the report.

If a machine in the region has a documentation target = 1 but the actuals = 0 then the details are dsiplayed,

otherwise they are not.

The dax and report visual is easy  .... but it took me a long time to do the powery query.

so please click the thumbs up and accept the solution. Thank you.

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 ! 

[Avian65]

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)

[speedramps]

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.

[Avian65]

I already did to your original solution and thanks for your support.

[speedramps]

Thank you. It was a fun Power Query problem.

Hope you learned some new skills from the solution.

[speedramps]

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

[speedramps]

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

[Avian65]

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