cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
FrankZappasMama Frequent Visitor
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
erik_tarnvik Established Member
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]))
erik_tarnvik Established Member
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],
    #"Promoted Headers" = Table.PromoteHeaders(Projects_Sheet, [PromoteAllScalars=true]),
    #"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"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Costs", "Keep", each [Costs.Date] <= [Start]),
    #"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
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?


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
erik_tarnvik Established Member
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:

image.png

 

Hope this helps!

 

 

erik_tarnvik Established Member
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]))
v-ljerr-msft Super Contributor
Super Contributor

Re: Calculations in Custom Column with LOOKUP and IF

Hi @FrankZappasMama,

 

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. Smiley Happy

 

Regards

FrankZappasMama Frequent Visitor
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

FrankZappasMama Frequent Visitor
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....

 

PowerBI 1.PNG

erik_tarnvik Established Member
Established Member

Re: Calculations in Custom Column with LOOKUP and IF

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

v-ljerr-msft Super Contributor
Super Contributor

Re: Calculations in Custom Column with LOOKUP and IF

Hi @FrankZappasMama,

 

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. Smiley Happy

 

c2.PNG

 

Regards

FrankZappasMama Frequent Visitor
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 Smiley Embarassed