User Profile
Rune2
New Member
Joined 1 year ago
User Widgets
Contributions
Re: Combined columns as primary key, allowing for duplicate values in the columns being combined
Thomas, I have now downloaded the pbix, learnedfrom it and implemented and tested in the "live" solution. My learning is that data modelling and how Power BI finds relations is important. Thus I have followed your suggested normalization of the resource table. I also have learnt that in some cases it works better with a measure than a column, in particular when retrieving values across tables. Thank you for the time you spent in this!1.1KViews1like1CommentRe: Combined columns as primary key, allowing for duplicate values in the columns being combined
Tomas, Thanks a lot for your quick and good reply. I notice that you have normalized resources in a separate table and defined a one-to-many relation from forecasttable and pricingtable to resources. You also have used AVERAGEX instead of MAX and turned around the sequence of CALCULATE and AVERAGEX and used SELECTEDVALUE for the rows in the Forecast table. I will try these changes in the model and see if they resolve the problem.1.2KViews1like0CommentsCombined columns as primary key, allowing for duplicate values in the columns being combined
I need help to fix this error message while updating data: “The column ‘Resource in the table ‘Table1’ contains a duplicated value ‘Value 1’, which is not allowed for columns on one side of many-to-one relationships or for columns used as a primary key for a table.” The unique key in Table1 is a combination of the columns "Resource", "Startdate" and "Enddate", meaning that there will be duplicated records in the column "Resource" for different "Startdate" and "Enddate" (ResourcePrice will be different for different time periods): Resource Startdate Enddate ResourcePrice Resource1 20240101 20240531 $1 000 Resource1 20240601 20241231 $2 000 Resource2 20240101 20241231 $1 500 The values in Table1 is queried and used in a calculated column in "Table2" (Table2 contains a budget based on quantity*ResourcePrice). The calculated column in "Table2" fetches "Price" from "Table1" where "Resource"="Resource" and "Date" >= "Startdate" and <="Enddate". The intention is to lookup the correct price for the given resource and date and use it to calculate the cost. ResourcePrice = CALCULATE( MAX(Table1[Timepris]); FILTER( Table1; Table1[Resource] = [Resource] && [Date] >= Table1 [Startdate] && [Date] <= Table1[Enddate] ) ) I have tried to fix the error message by combining "Resource", "Startdate" and "Enddate" in a combined column. Since Table1 contains "Startdate" and "Enddate" I need to use logical operators (<>=) to find the right "Resourceprice". I can't see that this is possible in a combined column and I have therefore created a new Table3 to generate all dates between "Startdate" and "Enddate" and combine them with "Resource": Table3 = ADDCOLUMNS( CROSSJOIN( Table1; GENERATESERIES(MAX(Table1[Startdate]);MAX(Table1[Enddate])) ); "Date";[Value] ) The intention is to fetch the Resourceprice from Table3 instead of Table1, using the combined column as the unique column, to avoid the problem that values in the "Resource" column is not unique. ResourcePrice = CALCULATE( MAX(Table3[Resourceprice]); FILTER( Table3; Table3[CombinedColumn] = Table2[CombinedColumn] ) ) I find it strange that I still get the same errormessage that "The column ‘Resource in the table ‘Table1’ contains a duplicated value ‘Value 1’", even when I delete all calculated columns that queries Table1 and inactivate alle relations to Table1. This all seems far to complicated in order to fetch a resource price from one table to another. Do you have a suggestion on a simpler approach? If not, do you have a suggestion on how to avoid the error message?Solved1.3KViews0likes4Comments
Data Privacy
Microsoft Fabric Community and Privacy
To learn more about how we manage your data, please review the Microsoft Fabric Community Data Privacy guide.