Forum Discussion

Thigs's avatar
Thigs
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 Many Fit =

(INT('Box Dimensions'[Selected Box Length]/'Item Dimensions'[Length (CM)])) *

(INT('Box Dimensions'[Selected Box Width]/'Item Dimensions'[Width (CM)])) *

(INT('Box Dimensions'[Selected Box Height]/'Item Dimensions'[Height (CM)]))
 
Basically, the same thing for Length, Width and Heigh - I'm finding the number of items that can fit in the length, width or height by dividing the dimensions of the box by the dimensions of the item. I have a couple tables going on here - 
 
Item Dimensions - This is a table containing SKU number, the item dimensions, and item description
 
Box Dimensions - This is a created table that gives the dimensions of my sizes of boxes - small, medium, and large. 
 
Selected Box Height/Width/Length are measures that say what the selected box size's length, width, etc are. 
My thought is that the user can see how many products fit in a large/medium/small box. There are no errors popping up, but when I run this, the tables, graphs, etc are all blank. 
 
Any help would be greatly appreciated!
 
  • 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

     

11 Replies

  • VahidDM's avatar
    VahidDM
    Super User
    4 years ago

    Hi Thigs 

     

    Can you post sample data as text and expected output?
    Not enough information to go on;

    please see this post regarding How to Get Your Question Answered Quickly:
    https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.
    4. Relation between your tables

    Appreciate your Kudos!!
    LinkedIn:www.linkedin.com/in/vahid-dm/

    • Thigs's avatar
      Thigs
      Helper IV
      4 years ago

      Hi! Thank you!

       

      Here are the two tables I'm using - 

      This is the 'Box Dimensions' table :

      Box TypeLength

      Width

      		Height
      Small6 45
      Medium866
      Large1087

       

      And this is 'Item Dimensions'

       

      SKU #Item NameLengthWidthHeightWeightCustom Column - How Many Fit
      1001Apple6552 
      1002Orange5441 

       

      I am trying to find the "How Many Fit" column. That is the output I am trying to find. I want to find out how many apples fit in each size box. 

       

      Basically, I want the user to be able to specify "Hey, we're using the small boxes" on a selection pane. Then the results will all populate with how many apples or oranges fit in the small boxes. If later the emploee decides that medium boxes should be used, they can change the selection to "medium boxes" on the pane and the graphics, charts, etc will update to show how many apples and oranges fit in a medium box. 

       

      I'm using this tutorial that shows how to make a variable selection pane - 

      How to Create Power BI TOP N Report (Variable N, Show Top 10/50/100…) - YouTube

       

      Thanks so much for your help!

      • smpa01's avatar
        smpa01
        Community Champion
        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
      Helper 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
        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] )