Multiple table relationships - associating job numbers to multiple data lines.

[Anonymous]

I have two tables: one a lookup table of project details (fairly static, updated on an ad-hoc basis) and the other weekly cost data. The project details table looks like this:

TableProjectLookup
Project Name	Job Number 1	Job Number 2	Job Number 3
One	Job001	Job002	Job003
Two	Job004	Job005	Job006
Three	Job007	Job008	job009

And the Weekly cost data looks something like this:

TableWeeklyCostData
Job Number	Cost to Date	Revenue to Date
Job001	£100	£200
Job002	£150	£180
Job003	£120	£90
Job004	£180	£170

I've been trying for a while to create a column/measure that would allow me to simply display the total cost per project - i.e. for each project it needs to look up three seperate job numbers from the weekly cost table (Total cost for Project One would be sum of costs for Job1+Job2+Job3).  When I try in create relationships with tables I get myself confused with the fact that I can only seem to relate a job number to a single column so not sure how to create a relationship where it can look up all three.

I wonder if someone might provide a few hints/tips for functions to use?  

many thanks

[parry2k]

@Anonymous as a best practice, it will be easy if you unpivot your project table.

- transform data
- select project column table
- right-click, unpivot other columns it will add two columns, attribute, and value, rename these as per your requirement
- close and apply

set relationship between your project table and job cost table and job cost will be on one side of the relationship in one to many relationship, from here you can easily calculate the cost of your projects and slice and dice the data by project or job number.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[Anonymous]

@parry2k  appreciate the quick response!  That worked but it has also broken another part of my model (not unexpected as I only gave half of the detail in the original question).  

I also have another table that deals with forecast cost - it looks something like this and has been unpivoted from a combined series of excel input sheets (1 sheet per resource with a simple matrix of project names versus week-end dates):

TableResourceForecast
Project Name	Week Ending	Resource	Cost Centre	Hours	Job	Cost
Project 1	20/06/2020	Person 1	Cost Centre 1	10	Job 1	£50
Project 1	27/06/2020	Person 1	Cost Centre 1	10	Job 1	£100
Project 1	27/06/2020	Person 2	Cost Centre 2	5	Job 2	£100
Project 1	27/06/2020	Person 3	Cost Centre 2	5	Job 2	£100

Basically  Cost Centre is pulled from a Resource Lookup table (with data,rates etc for each resource) and Job was originally based on a SWITCH statement using Cost Centre (i.e. if cost centre 1 use the job number from the cost centre 1 in the (original version) of the project lookup table etc).  With the new unpivoted version of the project table I have three lines per project and therefore this method doesn't work. Is there an easy trick to solve this? 

I suspect this is more about how I am structuring my input data - but I can't see any easy way to do this as its almost like the Project Table is being used in two different ways if that makes sense. 

[v-deddai1-msft]

Hi @Anonymous ,

Or you can try to use LOOKUPVALUE function like below：

Measure =
LOOKUPVALUE (
    WeeklyCostData[Cost to Date],
    WeeklyCostData[Job Number], MAX ( ProjectLookup[Job Number 1] )
)
    + LOOKUPVALUE (
        WeeklyCostData[Cost to Date],
        WeeklyCostData[Job Number], MAX ( ProjectLookup[Job Number 2] )
    )
    + LOOKUPVALUE (
        WeeklyCostData[Cost to Date],
        WeeklyCostData[Job Number], MAX ( ProjectLookup[Job Number 3] )
    )

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[parry2k]

@Anonymous this is too hard to explain everything, one thing is for your model is not optimized and needs to be fixed to make it all work. You should follow the transformation and fix other parts of the report based on that.