Combined columns as primary key, allowing for duplicate values in the columns being combined

[Rune2]

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? 

[TomasAndersson · Accepted solution]

Yeah that sounds like a lot of work. I'm having a bit of a hard time to get my head around all that text, but let's try to focus on what result you're after. My understanding is that you have one table with pricing and date ranges, a second table with dates and quantities, and then want to calculate sales/revenue of quantity*price, using the relevant price for the date. Something like that?

I managed to get the following to work. You can download the pbix here and see if that helps you anyway. If not you can try to give me a few example rows of the other tables that you are using (such as the forecast table with quantities and dates) and we can continue from there.

[Rune2]

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!

[TomasAndersson]

Glad to hear it worked out!

[Rune2]

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.