cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Aerglo Frequent Visitor
Frequent Visitor

Lookup Price Range for Style

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 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team
Community Support Team

Re: Lookup Price Range for Style

@Aerglo,

 

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]
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
ManNVSM Member
Member

Re: Lookup Price Range for Style

Dear @Aerglo,

 

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.

Aerglo Frequent Visitor
Frequent Visitor

Re: Lookup Price Range for Style

Dear @ManNVSM,

 

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 

ManNVSM Member
Member

Re: Lookup Price Range for Style

@Aerglocheck your Messages.

 

Regards,

ManNVSM.

Highlighted
Community Support Team
Community Support Team

Re: Lookup Price Range for Style

@Aerglo,

 

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]
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Aerglo Frequent Visitor
Frequent Visitor

Re: Lookup Price Range for Style

Dear v-chuncz-msft,

 

It works Smiley Happy

 

Thank you

 

Kind regards

 

Kristina