Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Thigs
Helper III
Helper III

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!
 
1 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

 

smpa01_0-1637787494440.png

pbix is attached

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

11 REPLIES 11
VahidDM
Super User
Super User

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/

Hi! Thank you!

 

Here are the two tables I'm using - 

This is the 'Box Dimensions' table :

Box TypeLength

Width

Height
Small45
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!

@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

 

smpa01_0-1637787494440.png

pbix is attached

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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?

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

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
Super User
Super User

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

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?

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

 

AlexisOlson_0-1637786622834.png

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!

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

 

AlexisOlson_0-1637792840524.png

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.