How can I create this table or matrix?

[Anonymous]

Hi community, 
It's the second post I do for this. I'm very obfuscated 'cause I can't solve this yet...
I hope somebody can help me.
I have two tables;

Table A

Item	Request	OnSite Pcs	OffSite Pcs	Pcs to complete request
609048-109-10	455	230	630	225

Table B

Item	Pallet ID	Pallet Pcs
	8	72
609048-109-10	21	132
609048-109-10	33	102
609048-109-10	34	132
609048-109-10	86	132
609048-109-10	97	60

Table B is the OffSite Pcs of table A, but it's splitted in Pallets. 
If I order the Pallets ID ascending or descending, I need to create a Table or Matrix that will shows me only the Pallets that adds up enough pcs to complete the Requested Pcs. 

A Table like this;

Item	Pallet ID	Pallet Pcs
609048-109-10	8	72
609048-109-10	21	132
609048-109-10	33	102

Or a Matrix like this;

Item	Pallet ID	Pallet Qty
609048-109-10
	8	72
	21	132
	33	102

In this case, the Table or Matrix bring me the Pallets 8, 21, and 33, because the total Pcs of the three pallets is 306, and i need, 225 Pcs in table A to complete the Request

***(The colors on the tables are only to separate the data fields)

I've been trying for days and I doesn't achieve to think how to create this Table or Matrix.
I hope somebady can help me soon.

[edhans · Accepted solution]

See if this works for you. It returns a text box (scalar, or single, value) that is a field for a table.

This is the code behind the "Pallets to Get" measure.

Pallets to Get = 
VAR varQuantityNeeded =
    MAX( 'Table A'[Pcs to complete request] )
VAR varCurrentItem =
    MAX( 'Table A'[Item] )
VAR varPalletCumulative =
    ADDCOLUMNS(
        FILTER(
            'Table B',
            'Table B'[Item] = varCurrentItem
        ),
        "Cumulative Pcs", CALCULATE(
            SUM( 'Table B'[Pallet Pcs] ),
            VAR varCurrentPalletID =
                CALCULATE(
                    MAX( 'Table B'[Pallet ID] )
                )
            RETURN
                FILTER(
                    'Table B',
                    'Table B'[Item] = varCurrentItem
                        && 'Table B'[Pallet ID] <= varCurrentPalletID
                )
        )
    )
VAR varLastPalletID =
    MINX(
        FILTER(
            varPalletCumulative,
            [Cumulative Pcs] > varQuantityNeeded
        ),
        [Pallet ID]
    )
VAR varFinalTable =
    FILTER(
        varPalletCumulative,
        [Pallet ID] <= varLastPalletID
    )
VAR Result =
    CONCATENATEX(
        varFinalTable,
        "Pallet " & [Pallet ID] & ": " & [Pallet Pcs] & "pcs",
        ","
            & UNICHAR( 10 )
    )
RETURN
    Result

The varFinalTable variable (next to the last variable) returns this table in memory:

So the trick is how to get that into a visual. A measure must be a scalar value, and I cannot think of a good way to do it otherwise. I could create a calculated table, but that is on a per item basis. 

I might redo this in Power Query that will return similar tables that are loaded, but this will get you something at least. And if you have a lot of data, the Power Query solution would be really slow. I will have to tinker with it.

[Anonymous]

Thank you so much, it was very helpful to me.
Both answers give me that I need, but such as you said, the first solution works better because my data base is to big. 
Im very grateful.

[edhans]

Great @Anonymous - glad one of the solutions worked for you.

[Anonymous]

Hi @edhans 
I have another question, I hope you can help me again.
Is possible to edit the Measure that you give me, to do a "double sort"? A Sort, into the previous sort.
Let me show you an example.
Table A;

Item	Request	OnSite Pcs	OffSite Pcs	Pcs to complete request
609048-109-10	120	20	160	100

Table B;

Item	Pallet ID	Pallet Pcs
609048-109-10	7	10
609048-109-10	10	40
609048-109-10	12	20
609048-109-10	13	20
609048-109-10	9	20
609048-109-10	11	40
609048-109-10	8	10

The Measure will bring me the pallets 7, 8, 9, 10 and 11 because this pallets, in order to the sort by Pallet ID, sum 120 pcs to complete the 100 requested. Right?

But, Is possible to edit the Measure to sort first by Pallet Pcs, and then, by Pallet ID (Something like the next table). Because if it's possible,  the Measure could return me the Pallets 10, 11 & 9, this pallets sum 100 pcs.
Bring less pallets is such a better thing in financial and operative way. 
Is it possible?
I hope you can help me again... Have a good day!

Item	Pallet ID	Pallet Pcs
609048-109-10	10	40
609048-109-10	11	40
609048-109-10	9	20
609048-109-10	12	20
609048-109-10	13	20
609048-109-10	7	10
609048-109-10	8	10

[edhans]

Ok @Anonymous - here is the Power Query version. It returns a hastily prepared matrix that looks like this:

I created Table C, which was your Table B, but modified. The M code to do this is as follows:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjOwNDCx0DU0sARiJR0lCyA2N1KK1cGUMjIEEobG2CWNjUGSBjgkTfDotDDDI2lpDiTMDJRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, #"Pallet ID" = _t, #"Pallet Pcs" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Pallet ID", Int64.Type}, {"Pallet Pcs", Int64.Type}}),
    #"Added Enough Pieces" = 
        Table.AddColumn(
            #"Changed Type", 
            "Enough Pieces", 
            each
                let 
                    varItemNeeded = [Item],
                    varPalletId = [Pallet ID],
                    varPiecesNeeded = Table.SelectRows(#"Table A", each [Item] = varItemNeeded)[Pcs to complete request]{0}
                in
                List.Sum(
                    Table.SelectRows(#"Changed Type", each [Pallet ID] <= varPalletId)[Pallet Pcs]
                ) > varPiecesNeeded,
                type logical
        ),
    #"Added Pallet Needed" = 
        Table.AddColumn(#"Added Enough Pieces", "Pallet Needed", 
        each 
            let
                varLastPallet = 
                    List.Min(
                        Table.SelectRows(#"Added Enough Pieces", each [Enough Pieces] = true)[Pallet ID]
                    )
            in
            [Pallet ID] <= varLastPallet,
            type logical
        ),
    #"Filtered Rows To Keep What Is Needed" = Table.SelectRows(#"Added Pallet Needed", each ([Pallet Needed] = true)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows To Keep What Is Needed",{"Item", "Pallet ID", "Pallet Pcs"})
in
    #"Removed Other Columns"

Before filtering and removing columns, it returns this table in Power Query:

I want to keep the three rows in red. The next two steps just filter for that, then keeps only the first 3 columns.
I did the Enough Pieces and Pallets Needed in two columns because it is Friday afternoon and getting all of that logic in one column was making my head hurt this late in the week. 😁

I make no warrants however on the performance of the M code on a large (100K or larger) record set. And I hate the matrix I created, but now that you have a table to link to, you can format it or display it however you like.

Here is the PBIX file.

Please mark one or both as the solution if they help. Or tell me I am off my rocker and totally missed your objective! 
To adapt the M code to your model...
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[Anonymous]

Hi @edhans ,
It's me again, sorry for ask a lot...
With more help from the community, I modified a little bit the measure that you gave to me, to sort first by Pallet Pcs, and then by Pallet ID.
But how can I do it into the M code? Is it possible?
Let me show you...
The measure just sorting by Pallet ID is the column "Pallets to Get", but the measure sorting first by Pallet Pcs, and then, by Pallet ID brings to me less Pallets, is the column "New Pallets to Get 2"

Is possible to do this double sort but in the M code that you gave me?
I hope you can help me again and for the last time, LOL.