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
abdullah11
Regular Visitor

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

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

instrumentidver
HPLC_000671
LC MSMS-001032
LC MSMS-001054
LC MSMS-001571
LCMSMS_000051
LCMSMS_0001722
DEAC9019441
HPLC-DAD-001002


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

instrumentidstatus
HPLC_00067 
LC MSMS-00103Assigned
LC MSMS-00105Canceled
LC MSMS-00157Cancelled
LCMSMS_00005Close
LCMSMS_00017Draft
DEAC901944Hold
LC MSMS-00103Rejected
LC MSMS-00105Resolved
LC MSMS-00157Returned
LCMSMS_00005SectionHead
LCMSMS_00017SendBack
DEAC901944Supervisor
HPLC-DAD-00100Canceled


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?

1 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))
11.PNG
pls see the attachment below




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

7 REPLIES 7
ryan_mayu
Super User
Super User

what's the expected output?

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




expected output:

instrumentidverUnder maintenanceNumber of maintenance requests
HPLC_000671No1
LC MSMS-001032Yes2
LC MSMS-001054No2
LC MSMS-001571Yes2
LCMSMS_000051No2
LCMSMS_0001722Yes2
DEAC9019441Yes2
HPLC-DAD-001002No1

@abdullah11 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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"

@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))
11.PNG
pls see the attachment below




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.