Lookup Price Range for Style

[Anonymous]

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 

[v-chuncz-msft · Accepted 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]

Dear v-chuncz-msft,

It works 🙂

Thank you

Kind regards

Kristina

[Anonymous]

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.

[Anonymous]

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 

[Anonymous]

@Anonymouscheck your Messages.

Regards,

ManNVSM.