Index Aggregate Row Max Number

[Saxon10]

I have two tables named "Data" and "Report."
Within the Data table, there are columns such as Area Code, Type, Rack Length, Rack Width, Rack Height, and Volume, which is determined based on the rack dimensions.
The Report table includes columns like Area Code, Pack Length, Pack Width, and Height. The Area Code serves as a key relationship linking the two tables.

In the context of the Data table, my objective is to identify the appropriate rack type corresponding to a specific area code. To achieve this in Excel,

I employ the following formula:

=IF(COUNTA(C3:E3)=0,"",IF(COUNTIF(C3:E3,">0")<3,"ND",IFERROR(@INDEX(DATA!$B$2:$B$16,AGGREGATE(15,6,(ROW(DATA!$F$2:$F$16)-1)/(DATA!$F$2:$F$16=1/(1/MAX(((DATA!$C$2:$C$16>=$D3)*(DATA!$D$2:$D$16>=$C3)+(DATA!$C$2:$C$16>=$C3)*(DATA!$D$2:$D$16>=$D3)>0)*(DATA!$E$2:$E$16>=$E3)*(DATA!$A$2:$A$16=$B3)*DATA!$F$2:$F$16))),1)),"NA")))

I generated a volume helper column in the Data table, utilizing the rack dimensions.
The primary aim of this helper column is to identify the minimum volume in cases where multiple sizes are matched.

Example:

The helper column is designed to pinpoint the rack with the smallest volume among the three racks that can accommodate one of the boxes among the twelve available.

Now, I am exploring a DAX code option for a new calculated column to achieve the same result.
Data:

AREA CODE	TYPE	RACK WIDTH	RACK LENGTH	RACK HEIGHT	VOLUME
AB1	A1	420	600	440	11.00
AB1	A2	640	600	480	9.00
AB2	A3	890	1100	1330	2.00
AB2	A4	1335	1100	2350	1.00
AB3	A5	890	1100	390	5.00

Report:

MATRIAL	AREA CODE	PACK LENGTH	PACK WIDTH	PACK HEIGHT	TYPE(DESIRED RESULT)
1234	AB5	115	116	116	A10
6420	AB5	115	116	116	A10
87	AB5	117	114	100	A10
97	AB5	117	100	90	A10

File attached for your reference
https://www.dropbox.com/scl/fi/mhf06g88gpz1wkaqoljq8/MD1.xlsx?rlkey=ebyyaxlccbwuj08xyn37bra3p&dl=0
https://www.dropbox.com/scl/fi/3bzvncr8y8zjyy0mhd1wt/MD1_05122023.pbix?rlkey=4yqcvrtvxmuatfn7zpdyvfm...

[Anonymous]

I had a quick look;

I got the above with:

Type =

VAR CurrentAreaCode = 'Report'[Area Code]

VAR CurrentPackLength = 'Report'[Pack Length]

VAR CurrentPackWidth = 'Report'[Pack Width]

VAR CurrentPackHeight = 'Report'[Pack Height]

VAR PossibleRacks =

    FILTER(

        'Data',

        'Data'[Area Code] = CurrentAreaCode &&

        'Data'[Rack Length] >= CurrentPackLength &&

        'Data'[Rack Width] >= CurrentPackWidth &&

        'Data'[Rack Height] >= CurrentPackHeight

    )

VAR MinVolumeRack =

    MINX(

        PossibleRacks,

        'Data'[Volume]

    )

RETURN

    IF(

        ISBLANK(MinVolumeRack),

        "NA",

        CALCULATE(

            VALUES('Data'[Type]),

            'Data'[Volume] = MinVolumeRack

        )

    )

Which I think is on the right track, but there needs to be some adjustments somewhere perhaps, as there are significantly more no-matches than matches. I can try to take another look tomorrow if it helps, or perhaps the above will be a good starting point for you

[Saxon10]

@Anonymous , Thank you for your swift response. I look forward to your additional feedback and a solution for the unmatched rows.