Multiple Selected Values in DAX Formula

[Thigs]

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!

[VahidDM]

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]

Hi! Thank you!

Here are the two tables I'm using - 

This is the 'Box Dimensions' table :

Box Type	Length	Width	Height
Small	6	4	5
Medium	8	6	6
Large	10	8	7

And this is 'Item Dimensions'

SKU #	Item Name	Length	Width	Height	Weight	Custom Column - How Many Fit
1001	Apple	6	5	5	2
1002	Orange	5	4	4	1

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 · Accepted solution]

@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]

I guess I replied too soon - this is working in a chart, but when I try to use it as values for a bar chart, it is blank. Any ideas?

[Thigs]

THIS WORKED! Ah! I love it and I love you! Thank you so much!!!

[AlexisOlson]

I'm curious why you use the CALCULATE with FILTER and ALLSELECTED in your measure. Does it not work with just the MAX part?

As a side note, you can fit two apples if you rotate them to align Apple length with Box height. (The possibility of rotations is why I have the six cases in my answer.)

[AlexisOlson]

A calculated column cannot be responsive to slicers since they are only computed once when the model is loaded or refreshed (not in response to user interaction).

I recommend checking out this prior thread:
https://community.powerbi.com/t5/Desktop/Find-Biggest-Middle-and-Smallest-Value/m-p/1996228

[Thigs]

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]

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]

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]

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