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

hi Please provide some sample data so that will try to resolve it — sanjay kumar
it seems that the data model needs some adjustment: (1) add an "incidentID" column to the Mutations table; (2) rename the "mutationID" column in the Incident table to "incidentID", which is related to the same-named column in the Mutations table — DAX0110
I have added the sample data here: ufile.io/6adju So in power pivot i want to add the correct values for Resolved by and Closed by — Bjorn Morrhaye
@DAX0110 I understand what you pointed out but it shouldn't make a big diffrence. I just renamed mutationID to incidentID — Bjorn Morrhaye

sanjay kumar sanjay kumar · Accepted Answer · 2018-01-11T13:07:10

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!

Lookup Values in Power Pivot tables with DAX

2 Answers