## Calculating between tables [Solved]

Anything related to PowerPivot and DAX Formuale
kalspiros
Posts: 3
Joined: Mon Oct 19, 2020 5:23 am

### Calculating between tables

Hi all,

I have this model in place and I'm looking for a DAX idea
Quick overview of tables:
ps: ignore Units, Phase Personnel, Meterage

Personnel_Rates:
Project Manager, Weekend, £20
Project Manager, Weekday, £15
Surveyor, Weekend, £18
...

Assets:
ATS
Retros
...

Cost_Personnel:
ATS, 40h, Project Manager, Weekend
ATS, 10h, Project Manager, Weekday
Retros, 5h, Project Manager, Weekday
...

Location Assets:
ATS, 3
Retros, 10
...

So, I want to (Personnel_Rates[Rate]) * (Cost_Personnel[Hours]) * (Location Assets[Qty])
In a nutshell, some employees need x amount of time to install an ATS. There are several ATS's per location
(Personnel_Rates[Rate]) * (Cost_Personnel[Hours]) is not an issue, it's a SUMX that lives in (Cost_Personnel) and multiplies the two.
Not sure how to "travel" upstream and then downstream to the (Location Assets) where the [Qty] lives on.

I can always start merging in Power Query and that does the trick but I prefer the efficiency of DAX as this dataset will grow big.

Also, if from your pov you can see a different iteration data analyst-wise, happy to change the model!

Attachments
2020-10-18.png (30.72 KiB) Viewed 291 times

MattAllington
Posts: 1150
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

### Re: Calculating between tables  [Solved]

It is a different use-case, but it is the same behaviour. You are using Excel, so there are no bidirectional filters. But you can turn on a bidirectional filter inside a calculate. I don’t cover that in the article, but the black magic will work for you

Alternatively you can make the relationship between cost_personnel and assets using

Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

kalspiros
Posts: 3
Joined: Mon Oct 19, 2020 5:23 am

### Re: Calculating between tables

Fantastic, thanks a mil Matt

I did suspect CROSSFILTER tbh but having the measure living in Assets was slightly confusing me on how to run calculations with Qty.
However, by refraining from using slicers that live in Personnel and Cost_Personnel, it does the trick!

Many thanks

kalspiros
Posts: 3
Joined: Mon Oct 19, 2020 5:23 am

### Re: Calculating between tables

Hi all (and Matt!)

I have carefully read the link you have suggested above and i attempted to use it for my model only to receive the well loathed "relationships" box. You can find the measures i have used in the screenshot below, both your suggestion and the black magic ones. I feel that i am not missing something obvious but apologies if i actually do. Can we identify the issue? and to make it even more complicated, i am looking to produce a pivot table that will be sliced by (Location [Associa...]) in rows and (Cost_Personnel [Phase Personnel]) in columns!! And the value will be a combination of (Personnel_Rates (Rate)], (Cost_Personnel[Hours]), (Assets[Units]) and (Location Assets[Qty]).

Matt, i have activated the first many-1 relationship by crossfiltering from (Cost_Personnel) up to (Assets) but i now struggle to slice by (Location[Associa...])

Is this an annoyingly complicated model and should i consider getting rid of it?

Attachments
2020-10-24 00_34_55-.png (82.65 KiB) Viewed 256 times

MattAllington
Posts: 1150
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

### Re: Calculating between tables

The yellow warning is a false positive. It’s not you, it’s Excel. You can’t turn it off. As for the 2 measures you show in the last post, there is a table “instrumentations” that is not in the model view you posted, so I can’t comment.
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

### Who is online

Users browsing this forum: No registered users and 2 guests