Crating a custom relationship between two tables

[Deepak_]

Hi Community Members,

I am looking to create a custom relationship between two tables. The tables I use are the pricing table and the sales table.
I have to perform some pricing calculations in the sales table based on the minimum and maximum range columns of the pricing table.

Initially, I manually created a pricing key in the pricing table based on the amount range and a custom column in the sales table as follows:

if [#"Amount"] >=0 and [#"Amount"] <= 24999 then "0-24999" else if [#"Amount"] >= 25000 and [#"Amount"] <= 249999 then "25000-249999" else if [#"Amount"] >= 250000 and [#"Amount"] <= 499999 then "250000-499999" else if [#"Amount"] >= 500000 and [#"Amount"] <= 749999 then "500000-749999" else if [#"Amount"] >= 750000 and [#"Amount"] <= 999999 then "750000-999999" else if [#"Amount"] >= 1000000 and [#"Amount"] <= 1249000 then "1000000-1249000" else if [#"Amount"] >= 1250000 and [#"Amount"] <= 100000000 then "1250000-10000000" else null

But the values I created key upon are going to change in the future. So I want to make sure the key I am using for the relationship dynamic. 

Is there a way in Power BI to make this custom key dynamic to adapt to future changes? I want to establish a flexible relationship between the two tables.

Thanks in advance

[Data-estDog]

I would need examples of the data sets (sanitized if not already). But I suspect I would not invent a releationship in this case (Again, I don't know what calculations you are doing). I imagine you would want your relationship to be based on product or service (what ever you are pricing and selling). Then you RELATED/RELATEDTABLE to perform your calculations based on the min/max range columns. 

[Deepak_]

Hi @Data-estDog,

This is the pricing table I am using. "Pricing_Key" is the custom column I created by merging the Volume minimum and maximum columns and using "-" as the delimiter.

And this is the sales table I am using:

Account ID	Company Name	Year	Month	Amount	Amount_Key
12254	Company A	2020	1	5120	0-24999
12537	Company B	2020	9	24878	0-24999
29954	Company C	2020	7	100000	24999-249999
52954	Company D	2020	11	500000	500000 - 749999

"Amount_Key" is the key I have created based on the amount range cuz I want to charge customers by the amount they belong to. However, the pricing table column's values will not remain the same. 
So, the relationship I have created will break in the future. Is there a way to solve this? 

[Data-estDog]

This sound more like a transaction system problem (logic driving pricing decision). The logic for this should be in the source system database and/or associated app. 

I say this because if you want, for instance, company A to have a range key of "0-24999" now. But then 6 months from now they increase their volume, and their key would change to (for example) "500000 - 749999". Your probem is that all the old records were generated prices using the old range. 

I suppose you could be working in an environment where they want you to hand them a report of customers and last months volume so they know how to input sales into the system. In that case, just make the Year/Month (Period) part of the table unique key, and instead of just adding the range, show the multiplier too. The AmountKey and multiplier should never change cause it is hard wired to the company/account and period. 

Account ID	Company Name	Period	Amount	Amount_Key	Pricing muliplier
12254	Company A	202001	5120	0-24999	1
12537	Company B	202009	24878	0-24999	.95
29954	Company C	202007	100000	24999-249999	.93
52954	Company D	202011	500000	500000 - 749999	.91

Edit: I noticed after I posted you said that pricing table values will change with time. To me this really scream ETL/ELT logic into your data hub/warehouse/lake. Your pricing table should have effective dates. It is a slowly changing dimension at this point.

[Deepak_]

Yes, I was also thinking the same. For making custom relationships at least one column's value should be constant. Still, I was wondering If there is a way to make the key dynamic ?

[Data-estDog · Accepted solution]

No. A foreign key is a deterministic relationship between records. 
You can derive key values from other columns as you are doing. So you could add year and month to your key, if you have the equivilent on your pricing table. But the one record will still only have one value at a time. 

[Deepak_]

Ok, I understand. Thanks for your help @Data-estDog.