Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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())
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
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
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.
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())
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
126 | |
111 | |
100 | |
83 | |
71 |