Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
sistancy
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
Thanks!
 
Best regards
Marius
3 REPLIES 3
lbendlin
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

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.