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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Avian65
Helper I
Helper I

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

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

table1.JPG

 

 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.

 

table2.JPG

 

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

 

table3.JPG

 

I want to create a column or measure, Please help.

 

Regards

Avian

1 ACCEPTED SOLUTION
speedramps
Super User
Super User

Please try this ...

Click here to download a solution 

 

speedramps_1-1662825065248.pngspeedramps_2-1662825066576.png

 

 

How it works:-

 

I used Power Query to transform your tabels to a star schema

 

speedramps_3-1662825131885.png

 

You can learn how by looking at the queries ...

 

speedramps_4-1662825280533.png

 

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.
 
 
speedramps_6-1662825512022.png

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 ! 

 

 

View solution in original post

8 REPLIES 8
speedramps
Super User
Super User

Please try this ...

Click here to download a solution 

 

speedramps_1-1662825065248.pngspeedramps_2-1662825066576.png

 

 

How it works:-

 

I used Power Query to transform your tabels to a star schema

 

speedramps_3-1662825131885.png

 

You can learn how by looking at the queries ...

 

speedramps_4-1662825280533.png

 

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.
 
 
speedramps_6-1662825512022.png

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.

speedramps
Super User
Super User

speedramps_0-1662819020472.png

 

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
Super User
Super User

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

Level1Level2MandatoryRegion
User ManualMachine Instruction manualYesNAFTA
Mechanical drawingsMechanical drawingsYesAPAC
Mechanical drawingsFixtures schematicsNo 
Mechanical drawingsLay-out designNo 
Electrical schemaElectrical scheme designYesEMEA
Electrical schemaElectrical panels Layout drawingsNo 
Electrical schemaMain control system documentationNo 
Machine SchemaMachine Schema DesignYesEMEA
Safety documentationCertification issued by Engineer officiallyYesEMEA
Safety documentationRisk assessment documentationYesAPAC
Safety documentationCertifications of systemsNo 
Safety documentationDisaster Recovery procedureYesNAFTA
MaintenancePreventive Maintenance ManualNo 
MaintenanceMaintenance manualNo 

 

Table 2

Machine CodePlant RegionLevel1Level2
MC00001NAFTAMechanical drawingsFixtures schematics
MC00001NAFTAMechanical drawingsLay-out design
MC00001NAFTAMaintenanceMaintenance manual
MC00002EMEAElectrical schemaElectrical scheme design
MC00002EMEAMechanical drawingsFixtures schematics
MC00003EMEAElectrical schemaMain control system documentation
MC00004APACMaintenanceMaintenance manual
MC00004APACMaintenancePreventive Maintenance Manual
MC00004APCElectrical schemaElectrical panels Layout drawings

 

Table 3

Machine CodeRegionMising Level 2
MC00001NAFTAMachine Instruction manual
MC00001NAFTADisaster Recovery procedure
MC00002EMEAMachine Schema Design
MC00002EMEACertification issued by Engineer officially
MC00003EMEAElectrical scheme design
MC00003EMEAMachine Schema Design
MC00003EMEACertification issued by Engineer officially
MC00004APACMechanical drawings
MC00004APACMechanical drawings

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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