One table values to another, new column, replacing codes

[Anonymous]

Hi,

I have two tables:

1. GAP(gap_value, gap_label, gap_value_ref) - Gap value is just a code, and label is its representation. Gap_value_ref is in an indicator which table, and which column it refers to.

sample data:

gap_value,    gap_label,                    gap_value_ref

1,               Customer,                      incident_customertypecode-1

2,               Supplier,                        incident_customertypecode-2

1,               Customer Complaint,    incident-casetypecode-1

2,               Customer Problem,       incident-casetypecode-2

2.Incident (id, casetypecode, customertypecode, gap_value_ref_caseType, gap_value_ref_customerTypecode)

Sample data:

id  casetypecode, customer type code   gap_value_caseType           gap_value_customerTypeCode

1           1                             1                 incident-casetypecode-1    incident_customertypecode-1

2           1                             2                 incident-casetypecode-1    incident_customertypecode-2

3           2                             1                 incident-casetypecode-2    incident_customertypecode-1

4           2                             2                 incident-casetypecode-2    incident_customertypecode-2

How can I replace / create additional column that would bring gap_label value from table GAP into INCIDENT? Any ideas? 

Thanks!

[TomMartens]

Hey,

can you please provide how the additional column in your table INCIDENT should be filled.

Regards

Tom

[Anonymous]

hey,

the code value in table INCIDENT should be replaced with gap_label from GAP

[TomMartens]

Hey,

sorry for not being precise, please provide the complete table INCIDENT after the replacement happened. This will help us to come up with a solution.

Regards

Tom

[Anonymous]

The final result to be something like this:

id               casetypecode,              customer type code        gap_value_caseType           gap_value_customerTypeCode

1          Customer Complaint                   Customer               incident-casetypecode-1    incident_customertypecode-1

2          Customer Complaint                    Supplier                 incident-casetypecode-1    incident_customertypecode-2

3           Customer Problem                     Customer                incident-casetypecode-2    incident_customertypecode-1

4           Customer Problem                     Supplier                   incident-casetypecode-2    incident_customertypecode-2

[TomMartens · Accepted solution]

Hey,

I would create two calculated columns in the INCIDENT table using DAX:

CaseTypeLabel = 
    LOOKUPVALUE(GAP[gap_label],GAP[gap_value_ref],'INCIDENT'[gap_value_caseType])

and

CustomerTypeLabel = 
    LOOKUPVALUE(GAP[gap_label],GAP[gap_value_ref],'INCIDENT'[gap_value_customerTypeCode]) 

then you get an INCIDENT table that looks like this

Hope this is what you are looking for

Regards

Tom

[Anonymous]

Thanks, very much, that worked for me!