Forum Discussion

Saxon10's avatar
Saxon10
Post Prodigy
2 years ago

Index Aggregate Row Max Number

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 CODETYPERACK WIDTHRACK LENGTHRACK HEIGHTVOLUME
AB1A1420600440   11.00
AB1A2640600480     9.00
AB2A389011001330     2.00
AB2A4133511002350     1.00
AB3A58901100390     5.00


Report:

MATRIALAREA CODEPACK LENGTHPACK WIDTHPACK HEIGHTTYPE(DESIRED RESULT)
1234AB5115116116A10
6420AB5115116116A10
87AB5117114100A10
97AB511710090A10


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=4yqcvrtvxmuatfn7zpdyvfmm9&dl=0

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Saxon10
      Post Prodigy
      2 years ago

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