I have a data set with categories, Styles and Prices. I need to cluster the prices in Price Ranges on Category level.
I have 5 Price Ranges per category and 12 categories, and 10 countries with local currency. If a Styles is a knit with Price 29,95 Euro I need it to be placed in Knit, Price Range 19,99 - 29,99 Euro.
I am quit sure I need to create a relationship between the two tables, but do not know how to...
Example of Data: (Made up numbers and names)
Month Country Category Style Name RRP Lookup Price Range (Need to find)
08.2018 CHE Knit VMWally knit 19,95 9,99 - 19,99
08.2018 DEU Knit VMSisse knit 29,95 19,99 - 29,99
09.2018 DEU Outerwear VMKubra outerwear 59,95 49,99 - 59,99
.... ... ......... ........... ....... ..........
Country Category Min Max Price Range
DEU Knit 0 9,99 0 - 9,99
DEU Knit 10 24,99 10 - 29,99
.... ..... .... .... .....
CHE Outerwear 0 19,95 19,99
.... ....... ...... ..... .....
So I need it to lookup first the country, then the category and then which Price Range the Price is in.
I hope it makes sense what I am trying to do.
Go to Solution.
You may refer to the following DAX that adds a calculated column.
Table2[Country] = Table1[Country]
&& Table2[Category] = Table1[Category]
&& Table2[Min] <= Table1[RRP]
&& Table2[Max] > Table1[RRP]
View solution in original post
I'm sorry but the relationship between 2 table, must have at least 1 column have the unique key. In your case, you can't create relationship.
Can you share you .pbix file (with dummy data or delete sensitive data)? Maybe I'll need it for solve by another way.
Thanks & Regards,
Can I send you an Email with fake data? I do not know how to attach the file here...
Thank you for taking a look at it.
@Aerglocheck your Messages.
It works 🙂
Join us in the third Triple A event!
It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.
Make sure you didn't miss any of the things that happened in the community in January!