Lookup Values in Power Pivot tables with DAX

Question

i am trying to create a calculated column in Excel which should contain values from related tables. I tried looking into the Lookup function but this looks too complicated for my knowledge.

I have an Incident table where i want to have a calculated Column named closdBy. In this column i want to put the name of the operator that closed the incident. Every incident has many mutation entries which are stored in a Mutations table. This Mutations table has 1 statusID which comes from another related table named Status

In the calculated Field closedBy i want the calculated column to display the Name of the operator whose name matches the statusID Closed The name of the operator comes from the Operator table.

Anyone can help me with this ? Would appreciate it a lot.

POWER BI Example: https://ufile.io/z0g4x

Excel Example: https://ufile.io/6adju

Answer 1

This is how my table looks like. The mutation table can have the same value for status (see {CLO} but I want to have the field Closed By and Resolved by in the Incident table to display the most recent Operator.

I have here an adjusted PowerBi file with more data: https://ufile.io/20u0v

Answer 2

required result in incident table (download PowerBI file) :

I have made some modification in relationship between table incident and mutation

created a column in mutation table ID_Closed = IF(Mutation[statusID]="{CLO}",Mutation[ID],CONCATENATE(Mutation[ID],Mutation[statusID]))

Hoping this will help!