Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
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)
Report 1:
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
.... ... ......... ........... ....... ..........
Report 2:
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.
Thank you,
/Kristina
Solved! Go to Solution.
@Anonymous,
You may refer to the following DAX that adds a calculated column.
Column = MAXX ( FILTER ( Table2, Table2[Country] = Table1[Country] && Table2[Category] = Table1[Category] && Table2[Min] <= Table1[RRP] && Table2[Max] > Table1[RRP] ), Table2[Price Range] )
@Anonymous,
You may refer to the following DAX that adds a calculated column.
Column = MAXX ( FILTER ( Table2, Table2[Country] = Table1[Country] && Table2[Category] = Table1[Category] && Table2[Min] <= Table1[RRP] && Table2[Max] > Table1[RRP] ), Table2[Price Range] )
Dear v-chuncz-msft,
It works 🙂
Thank you
Kind regards
Kristina
Dear @Anonymous,
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,
ManNVSM.
Dear @Anonymous,
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.
Kind regards,
Kristina
@Anonymouscheck your Messages.
Regards,
ManNVSM.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |