cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Calculations in Custom Column with LOOKUP and IF

Folks,

I'm struggling to create a custom column to the Project List dataset (explained below) that needs to lookup two values in the Project Cost dataset and return it. There are three relevant datasets all loaded into the PowerBI project that I need to work with:

• Project List (one row per project, hundreds of rows)
• Project Hours (multiple rows per project, thousands of rows)
• Project Cost (few dozen rows based on one cost per quarter per category)

I'm able to easily link the Project List and Project Hours and then create reports as:

• Project List contains each project in a single row with a unique project id
• Project Hours contains multiple rows for each project all referencing the project id

However, I'm struggling to deal with Project Cost. Each projects cost should by obtained by referencing Project Category and Project Start Date in the Project Cost dataset which contains four columns:

• Dvision (irrelevant for this)
• Start Date (one cost per category per quarter)
• Category (3 types)
• Cost (\$/hr)

Just for the sake of clarity, each project will only have a single cost (\$/hr) which is determined by when it started and which category it is. In the Project Cost dataset there are three Costs listed for each quarter, ie. one for each category. I just need to pull the right one across to the Project List so I can then work with it in the Reports and Visualizations.

Can someone help me write the Custom Column formula that will Lookup the correct Project \$/hr based on Project Category and Project Start Date in Project List please?

Cheers,

FZM

2 ACCEPTED SOLUTIONS

Accepted Solutions
Established Member

## Re: Calculations in Custom Column with LOOKUP and IF

Another way, using the same sample data, is to use the TOPN function:

```ProjectCost2 =
CALCULATE(MAX(Costs[Cost]),
TOPN(1,
FILTER(Costs,Costs[Category] = Projects[Category] &&
Costs[Date] <= Projects[Start]),
Costs[Date]))```
Established Member

## Re: Calculations in Custom Column with LOOKUP and IF

Of course. Bare with me though as this may appear a little more complex. I could post just the "M" code (you can see it below) but I assume it to be more helpful to lead you through the steps in the GUI to get there. Apply these steps:

1. Use Get Data to load your Projects and Costs tables and edit in Power Query. Select the Projects table.

2. Select Merge Queries. Select the Category table from Projects, select the Costs table in the dropdown, select the Category column in the Costs table and select an Inner Join in the drop down. Click OK.

3. Expand the resulting tables in the Costs column in the result by clicking the icon to the right in the Costs column header.

4. Now we need to only keep rows where Costs.Date is equal or earlier than Start date. Create a new custom column named Keep defined as [Costs.Date] <= [Start]. Click the filter button in the Keep header and unselect FALSE.

5. Click Group By under transform. Click Advanced. Select ID as your Group By column. Enter CostDate as your New column name, select Max as you operation and select Costs.Date as Column. Click Add aggregation to get a new row. Enter Start, Max, Start for the fields. Click Add aggregation again. Enter Category, Max, Category. Click Add aggregation again. Enter ProjectCost, MAX, Costs.Cost. Repeat this as necessary if you have other columns you need to have in your result. Click OK.

Done. You can now remove/reorder/rename columns as appropriate. If you click Advanced Editor you can see the resulting "M" code, it should look something like this:

```let
Source = Excel.Workbook(File.Contents("D:\OneDrive\file.xlsx"), null, true),
Projects_Sheet = Source{[Item="Projects",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", type text}, {"Start", type date}, {"Category", type text}, {"Sum", Int64.Type}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Category"},Costs,{"Category"},"Costs",JoinKind.Inner),
#"Expanded Costs" = Table.ExpandTableColumn(#"Merged Queries", "Costs", {"Category", "Date", "Cost"}, {"Costs.Category", "Costs.Date", "Costs.Cost"}),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Keep] = true)),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"ID"}, {{"CostDate", each List.Max([Costs.Date]), type date}, {"Start", each List.Max([Start]), type date}, {"Category", each List.Max([Category]), type text}, {"ProjectCost", each List.Max([Costs.Cost]), type number}})
in
#"Grouped Rows"```

Good luck, let me know if you run into issues.

15 REPLIES 15
Super User

## Re: Calculations in Custom Column with LOOKUP and IF

@FrankZappasMama is it possible to see the relationship diagram if its available in order to see how you would link this?

are there any relationships set up?

Proud a to be a Datanaut!
Established Member

## Re: Calculations in Custom Column with LOOKUP and IF

Assuming the following tables:

```Projects:
ID	Start	        Category
A	1/10/2017	Z
B	4/10/2017	Z
C	1/10/2017	X
D	4/10/2017	X

Costs:
Category	Date	        Cost
Z	        1/1/2017	100
Z	        4/1/2017	150
X	        1/1/2017	200
X	        4/1/2017	250```

I further assume the logic to be that the cost of Project A should be the Cost in the Costs table that has a date equal or earlier than the Start date where the categories match. This can be achieved with a calculated column in table Projects defined as follows:

```ProjectCost =
VAR CostDate =
CALCULATE(MAX(Costs[Date]),
FILTER(Costs,
Costs[Category] = Projects[Category] &&
Costs[Date] <= Projects[Start]))
RETURN
CALCULATE(MAX(Costs[Cost]),
FILTER(Costs, Costs[Date] = CostDate &&
Costs[Category] = Projects[Category]))```

This results in the following table Projects:

Hope this helps!

Established Member

## Re: Calculations in Custom Column with LOOKUP and IF

Another way, using the same sample data, is to use the TOPN function:

```ProjectCost2 =
CALCULATE(MAX(Costs[Cost]),
TOPN(1,
FILTER(Costs,Costs[Category] = Projects[Category] &&
Costs[Date] <= Projects[Start]),
Costs[Date]))```
Super Contributor

## Re: Calculations in Custom Column with LOOKUP and IF

Have you tried the solution provided by @erik_tarnvik above? Does it work in your scenario? If it works, could you accept it as solution to close this thread?

If you still have any question on this issue, feel free to post here.

Regards

Frequent Visitor

## Re: Calculations in Custom Column with LOOKUP and IF

PowerBI legends,

My apologies for not replying, my week has been hijacked by some other items so I have not yet tried these solutions.

I'm hoping to try this out tomorrow. Will revent then.

Thanks

Frequent Visitor

## Re: Calculations in Custom Column with LOOKUP and IF

@erik_tarnvik

Just found a few minutes to try this out BUT I'm getting a syntax error after adapting the formula you provided for the actual column names in my dataset. Seems it doesn't like the CostDate name....

Established Member

## Re: Calculations in Custom Column with LOOKUP and IF

Looks like you are missing an equal sign after VAR CostDate?

Super Contributor

## Re: Calculations in Custom Column with LOOKUP and IF

In addition, the formulas provided by @erik_tarnvik are DAX, not M query. So you should use them in Modeling Table -> New Column, instead of Query Editor -> Custom Column.

Regards

Frequent Visitor

## Re: Calculations in Custom Column with LOOKUP and IF

@v-ljerr-msft

....and there's the confirmation I'm punching above my weight with this question