Uphill Calculation with USERELATIONSHIP

[Anonymous]

Hi, 

What i have:

- a table of production orders called 'Productions', which contains Productionid, ItemId, etc.

- a table of Items, which contains Itemid, CurrentStandardCostPrice and other meta data.

- a ton of other tables in the model, that are irrelevant to this example.

- There is an indirect relationship between the 'Items'- and the 'Productions' table.

What i want:

A visualisation(table) in power bi, that shows Itemid(from the production table) and the related 'CurrentStandardCostPrice'

The problem:

From what i understand, one usually uses CALCULATE to create and uphill filter as such:

Production Standard Cost Price:=
CALCULATE(

SUM(Items[CurrentStandardCostPriceAcc])

,Productions)

BUT this method doesn't seem to work with an indirect relationship, even even using USERELATIONSHIP as such:
Production Standard Cost Price:=
CALCULATE(

SUM(Items[CurrentStandardCostPriceAcc]),

USERELATIONSHIP(Items[Partition-CompanyId-ItemId-Key], Productions[Partition-CompanyId-ItemId-Key])

,Productions)

Can the formula be used this way or am i missing something?

[Anonymous · Accepted solution]

I went with a measuer using LOOKUPVALUE in stead, which bypasses the need for a relationship between the models.

Production Standard Cost Price:=
IF(

HASONEVALUE(Productions[ItemId]),

LOOKUPVALUE(

Items[CurrentStandardCostPriceAcc],

Items[Partition-CompanyId-ItemId-Key],
VALUES(Productions[Partition-CompanyId-ItemId-Key])

),

BLANK())

[AlB · Accepted solution]

Hi @Anonymous 

Give this  a try:

CALCULATE (
    CALCULATE ( SUM ( Items[CurrentStandardCostPriceAcc] ), Productions ),
    USERELATIONSHIP ( Items[Partition-CompanyId-ItemId-Key], Productions[Partition-CompanyId-ItemId-Key] )
)

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Of course!

In hindsight, this makes a lot of sense.

I haven't used nested calculates a whole lot, so i'll definately remember this and use it going forward.

[Anonymous · Accepted solution]

I went with a measuer using LOOKUPVALUE in stead, which bypasses the need for a relationship between the models.

Production Standard Cost Price:=
IF(

HASONEVALUE(Productions[ItemId]),

LOOKUPVALUE(

Items[CurrentStandardCostPriceAcc],

Items[Partition-CompanyId-ItemId-Key],
VALUES(Productions[Partition-CompanyId-ItemId-Key])

),

BLANK())