Lookup Rate Code in RateTable and return the Amount for that Ratecode to the Fact Table.

[lvdnest3866]

Relationships

Hi there,

Please help. I have the Fact table (PRMSTable) and the lookup

table (Rate code extract).

In the PRMStable I have 4 different columns each with their own Rate codes.

PRMSTable

RateTable

I want to create 4 additional columns in the PRMSTable with the following column names:

Column 1: LabourHrs. The LabourHrs column should show the  Base amount from the Rate Table column RateCode that it gets from the Base Standard labor time rate code column in the PRMSTable.

Column 2: MachineHrs column should use the Base standard machine time rate code in PRMSTable and Rate Code from Rate Table and return the Base Amount.

Column 3: Indirect Labour Rate column should use the Base standard burden 1 rate code in PRMSTable and Rate Code from Rate Table and return the Base Amount.

Column 4: OverheadRate column should use the Base standard burden 3 rate code in PRMSTable and Rate Code from Rate Table and return the Base Amount.

Add each Base Amount against each Product Number

At the moment when I drag the Base Amount into the Pivot Table Row area every single Base Amount from the RateTable shows against duplicates of Product Number. 

4 left columns what I want to see, 4 right columns what rates to use 

I am not sure how to do the lookup and if I should go and create each of the 4 columns in the PRMSTable and then write the function for each of the columns.

Any assistance would be greatly appreciated.

[v-piga-msft · Accepted solution]

Hi @lvdnest3866，

There is a little complex for your case.

Based on your title, it seems that you're confused of using lookupvalue  function.

You could have a view of this video which introduce the scenarios about LookupValue.

If you need further, please share a dummy pbix file which can reproduce the scenario, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)

I would appreciate it if you could show your final desired output.

Best Regards，

Cherry

[lvdnest3866]

Hello Cherry.

Thank you so much. That was exactly what i needed. It was very easy to do.

I just did not know the DAX function to use, but now i will abuse it. 

I will upload my results soon.

Regards.