Forum Discussion

Thigs's avatar
Thigs
Icon for Helper IV rankHelper 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's avatar
    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

     

Thigs's avatar
Thigs
Icon for Helper IV rankHelper IV
4 years ago

Is there any way to get what I want? Where there is a way for the user to input what kind of box and it changes the report?

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper 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] )

     

    • Thigs's avatar
      Thigs
      Icon for Helper IV rankHelper IV
      4 years ago

      While I inputted the formula correctly, I'm still getting blanks when I put it in a chart, graph, etc? Any ideas? I super appreciate your help!

      • AlexisOlson's avatar
        AlexisOlson
        Icon for Super User rankSuper User
        4 years ago

        What are you trying to chart? It works fine for me.