Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

Problem with relations of tables and trying to do a measure

Hello everyone


My Power BI dashboard is intended to show costs of products monthly in the past by having monthly prices of the articles with are needed to build the product and the quantity of articles which are processed in the product. Therefore I need to calculate the total costs by multiplying the quantity of the articles for the selected product with the monthly prices for the articles. Thats why I thought to do it with the SUMX-Function like in the following:


Gesamtkosten = SUMX(Mengen, Mengen[Menge] * RELATED(EntpPreise[Preise])) 
or translated
total costs = SUMX(tblQuantity, tblQuantity[Quantity] * RELATED(tblPrices[Prices]))
But I get a error message like the following:
"The column "tblPrices[Prices]" either does not exist or has no relation to a table that is available in the current context."
I think the problem is based on the relations between my tables but I don't know a better way to design it
I added a screenshot of the relations (It is written in German but relations between the tables are based on columns with the same names) (Translation of the columns: "[Mengen]" = quantity, "[Preise]" = prices)Screenshot 2023-04-24 103314.png
I tried a n:m relation between "EntpPreise" and "Mengen" ("tblPrices" and "tblQuantity") but it doesnt work either.
I hope someone can help me out
Best regards
Super User
Super User

 show costs of products monthly in the past by having monthly prices of the articles with are needed to build the product and the quantity of articles which are processed in the product. 

Your EntpPreise table needs a Produkt column.

Thank you very much for your answer!
But I don't think so, because articles are needed to build the product and the prices are only based on these articles and don't depend directly on the product. Therefore products are only connected trough the quantity of the articles they need.
My problem is that its just not possible to calculate between quantity and price. It seems to me that Power BI just can't use the article number of the selected row from table "prices" go through the table "article" and find the same article number in table "quantity", or at least I don't know how to command it properly. I tried it in Access, there it is no problem at works completely fine.
To evade this problem I created a table with a VLOOKUP and several queries so prices and quantity are in one table and i can just calculate the costs in a extra column. This leads to many rows in this table, and doesn't seem like a proper database solution. But works currently...
If someone has other ideas I would be glad😊

In general the data model needs to follow the business scenario, not the other way round.  If you have a working solution that may not 100% conform to the star/snowflake mantra then so be it.

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors