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

Postby kalspiros » Mon Oct 19, 2020 6:50 am

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!

Many thanks in advance
Attachments
2020-10-18.png
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]

Postby MattAllington » Mon Oct 19, 2020 4:43 pm

If you are willing to read and learn, I suggest you read my full article here. https://exceleratorbi.com.au/many-many- ... explained/

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

CALCULATE(Your expression,CROSSFILTER(cost_personnel[key],assets[key],both))

Read the article first.
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

Postby kalspiros » Mon Oct 19, 2020 11:57 pm

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

Postby kalspiros » Sat Oct 24, 2020 9:47 am

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? :geek:

Many thanks in advance
Attachments
2020-10-24 00_34_55-.png
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

Postby MattAllington » Sun Oct 25, 2020 6:16 am

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


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 2 guests

cron