How to add a column that determines if the device is under maintenance

[abdullah11]

Hello
I have two tables, one in which there is a list of devices (Devices table):

instrumentid	ver
HPLC_00067	1
LC MSMS-00103	2
LC MSMS-00105	4
LC MSMS-00157	1
LCMSMS_00005	1
LCMSMS_00017	22
DEAC901944	1
HPLC-DAD-00100	2

The other contains a list of maintenance requests on the devices (Maintenance requests):

instrumentid	status
HPLC_00067
LC MSMS-00103	Assigned
LC MSMS-00105	Canceled
LC MSMS-00157	Cancelled
LCMSMS_00005	Close
LCMSMS_00017	Draft
DEAC901944	Hold
LC MSMS-00103	Rejected
LC MSMS-00105	Resolved
LC MSMS-00157	Returned
LCMSMS_00005	SectionHead
LCMSMS_00017	SendBack
DEAC901944	Supervisor
HPLC-DAD-00100	Canceled

How can I add a column to the first table (device list) that shows whether the device is available/unavailable because it is under maintenance, if there is a row in the second table and the status of the maintenance request is:
Assigned
Hold
Returned
SendBack

How can I put another column that shows the number of current maintenance requests?

[ryan_mayu]

what's the expected output?

e.g. for the second id, 003, there are two rows in the second table. 

[abdullah11]

expected output:

instrumentid	ver	Under maintenance	Number of maintenance requests
HPLC_00067	1	No	1
LC MSMS-00103	2	Yes	2
LC MSMS-00105	4	No	2
LC MSMS-00157	1	Yes	2
LCMSMS_00005	1	No	2
LCMSMS_00017	22	Yes	2
DEAC901944	1	Yes	2
HPLC-DAD-00100	2	No	1

[ryan_mayu]

@abdullah11 

could you pls let me know how to get yes/no? what's the logic?

[abdullah11]

yes/no Logic based on the presence of a line for the same “instrumentid” in the second table (maintenance requests), and “status” column must be = "Assigned" or "Hold" or "Returned" or "SendBack"

[ryan_mayu · Accepted solution]

@abdullah11 

pls try this

Column = if( countx(FILTER(RELATEDTABLE(Maintainance),'Maintainance'[status] in {"Assigned" , "Hold" , "Returned" , "SendBack"}),'Maintainance'[instrumentid])>0,"yes","no")

Column 2 = COUNTROWS(RELATEDTABLE(Maintainance))

pls see the attachment below

[abdullah11]

Okay, it worked!! Thank you very much. How could you do it without referring to the instrumentid column in the first table?

[ryan_mayu]

you are welcome. based on the attachment , you can see that i created relationship between two tables.