Using parameters table with calculations

[maring28]

Hi everyone,
I'm not so new to Power Pivot, however I've never needed to do this type of calculation:
I have many products with physical measures: length, width and height.
I need to determine for every product it's group, i.e, if a product's length = 40mm, width = 20mm, height=30mm, I need to lookup in a table which contains all grouped sizes, and determine the product group.
This product's group, according to the table is group number 3.
In excel I would've solved it with several IF's, selecting the relevant cells. In PP, however, I cannot do that. I don't even know how to connect this table to my main product table...
[TABLE="width: 423"]
<colgroup><col><col span="6"></colgroup><tbody>[TR]
[TD] [/TD]
[TD="colspan: 2"] length[/TD]
[TD="colspan: 2"] width [/TD]
[TD="colspan: 2"] height[/TD]
[/TR]
[TR]
[TD="align: left"]group[/TD]
[TD]MIN[/TD]
[TD]MAX[/TD]
[TD]MIN[/TD]
[TD]MAX[/TD]
[TD]MIN[/TD]
[TD]MAX[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0[/TD]
[TD]25[/TD]
[TD]20[/TD]
[TD]45[/TD]
[TD]8[/TD]
[TD]27[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]25[/TD]
[TD]36[/TD]
[TD]20[/TD]
[TD]45[/TD]
[TD]8[/TD]
[TD]42[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]36[/TD]
[TD]48[/TD]
[TD]20[/TD]
[TD]45[/TD]
[TD]8[/TD]
[TD]42[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]48[/TD]
[TD]60[/TD]
[TD]20[/TD]
[TD]45[/TD]
[TD]8[/TD]
[TD]42[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]60[/TD]
[TD]100[/TD]
[TD]20[/TD]
[TD]45[/TD]
[TD]8[/TD]
[TD]42[/TD]
[/TR]
</tbody>[/TABLE]

Please help!
Thanks in advance
Marina

 

[idan_Excelando]

Hi,

In order to do this, You will need to add a calculated column to the products table with the following DAX formula:
(let's say that the mapping table you shared above is named "Table2"

=CALCULATE(MIN(Table2[group]),FILTER(Table2,Table2[height-MIN]<=[height] && Table2[height-MAX]>=[height] && Table2[length-MIN]<=[length] && Table2[length-MAX]>=[length] && Table2[width-MIN]<=[width] && Table2[width-MAX]>=[width]))

 

[maring28]

Thanks for your answer!
I still don't understand how can I add a column to the products table with refference to Table2, if the tables are not connected by relations?
I see no way to connect the tables because there are no common keys here...
Thanks alot

 

[idan_Excelando]

Sometimes - relationships interfere , This method assume that the tables are disconnected.
I recommend you to read about disconnected tables in PowerPivot

 

[scottsen]

The technique is a bit odd in that you use the lookup table, to generate values that can be used to relate back to your lookup table

You can always reference other tables, regardless of having a relationship. It is simply that without a relationship, no filtering will happen.

Imagine you do a simpler, and less correct, calculated column on your data table...

=IF (Data[Length] <=25, 1,
IF (Data[Length] <= 36, 2,
...
))

You will then have a column you can use to relate these 2 tables, ya?

What idan_Excelando has done is made a similiar calculated column that says "let's use the values in this data row... and find the matching group id from the Measurements table". You can then still relate those 2 tables! It's fancy!

 

[maring28]

Thanks guys!
It's working!!!