Forum Discussion

Helper IV

4 years ago

Solved

Multiple Selected Values in DAX Formula

Hi all! I am working on a data set involving what number of items can fit inside a box. I am trying to make it so we can have variable box sizes. Here's my formula (Calculated Column) How Ma...

smpa01

4 years ago

Thigs do you mean this?

Measure = 
VAR _heightItem =
    MAX ( 'item'[Height] )
VAR _lengthItem =
    MAX ( 'item'[Length] )
VAR _widthItem =
    MAX ( 'item'[Width] )
VAR _heightBox =
    CALCULATE (
        MAX ( box[Height] ),
        FILTER ( box, box[Box Type] = ALLSELECTED ( box[Box Type] ) )
    )
VAR _lengthBox =
    CALCULATE (
        MAX ( box[Length] ),
        FILTER ( box, box[Box Type] = ALLSELECTED ( box[Box Type] ) )
    )
VAR _widthBox =
    CALCULATE (
        MAX ( box[Width] ),
        FILTER ( box, box[Box Type] = ALLSELECTED ( box[Box Type] ) )
    )
VAR _cal =
    INT ( DIVIDE ( _heightBox, _heightItem ) )
        * INT ( DIVIDE ( _lengthBox, _lengthItem ) )
        * INT ( DIVIDE ( _widthBox, _widthItem ) )
RETURN
    _cal

pbix is attached

_box.pbix32 KB

AlexisOlson

Super User

4 years ago

Yes. The post I linked to gives a solution.

Create this measure and it will adjust if you change the box type:

MaxFit = 
VAR BoxH = SELECTEDVALUE ( Boxes[Height] )
VAR BoxW = SELECTEDVALUE ( Boxes[Width] )
VAR BoxD = SELECTEDVALUE ( Boxes[Length] )
VAR ItemH = SELECTEDVALUE ( Items[Height] )
VAR ItemW = SELECTEDVALUE ( Items[Width] )
VAR ItemL = SELECTEDVALUE ( Items[Length] )
VAR Case1 = TRUNC ( BoxH / ItemH ) * TRUNC ( BoxW / ItemW ) * TRUNC ( BoxD / ItemL )
VAR Case2 = TRUNC ( BoxH / ItemH ) * TRUNC ( BoxW / ItemL ) * TRUNC ( BoxD / ItemW )
VAR Case3 = TRUNC ( BoxH / ItemW ) * TRUNC ( BoxW / ItemH ) * TRUNC ( BoxD / ItemL )
VAR Case4 = TRUNC ( BoxH / ItemW ) * TRUNC ( BoxW / ItemL ) * TRUNC ( BoxD / ItemH )
VAR Case5 = TRUNC ( BoxH / ItemL ) * TRUNC ( BoxW / ItemH ) * TRUNC ( BoxD / ItemW )
VAR Case6 = TRUNC ( BoxH / ItemL ) * TRUNC ( BoxW / ItemW ) * TRUNC ( BoxD / ItemH )
RETURN
    MAXX ( { Case1, Case2, Case3, Case4, Case5, Case6 }, [Value] )