Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Double Sort or Sort into another previous Sort ?

Hi community, Recently i posted for some help and edhans helped me with a measure. He gave me this measure that properly works for what i asked. Pallets to Get = VAR varQuantityNeeded = MAX(...
General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    HI  Anonymous 

    I have adjusted the formula as below:

    NEW Pallets to Get 2= 
VAR varQuantityNeeded =
MAX( 'Table A'[Pcs to complete request] )
VAR varCurrentItem =
MAX( 'Table A'[Item] )
----------------------------------------------
var _table1= ADDCOLUMNS(
FILTER(
'Table B',
'Table B'[Item] = varCurrentItem
),
"rank1", 
VAR varCurrentPalletID =
CALCULATE(
MAX( 'Table B'[Pallet ID] )
)
VAR varCurrentPalletPCS =
CALCULATE(
MAX( 'Table B'[Pallet Pcs] )
)
RETURN
 RANKX(
 FILTER(
 'Table B',
 'Table B'[Item] = varCurrentItem
 ),[Pallet Pcs],,DESC,Dense)+
RANKX(
FILTER(
'Table B',
'Table B'[Item] = varCurrentItem
),[Pallet ID],,ASC,Dense)/COUNTROWS('Table B')
)
return
-------------------------------
VAR varPalletCumulative =
ADDCOLUMNS(_table1,"Cumulative Pcs",SUMX(FILTER(_table1,[rank1]<=EARLIER([rank1])),[Pallet Pcs]))
--------------------------------------
VAR varLastrank =
MINX(
FILTER(
varPalletCumulative,
[Cumulative Pcs] >= varQuantityNeeded
),
[rank1]
)
VAR varFinalTable =
FILTER(
varPalletCumulative,
[rank1] <= varLastrank
)
VAR Result =
CONCATENATEX(
varFinalTable,
"Pallet " & [Pallet ID] & ": " & [Pallet Pcs] & "pcs",
","
& UNICHAR( 10 )
)
RETURN
Result

    Result:

     

    and here is sample pbix file, please try it.

     

    Regards,

    Lin

v-lili6-msft's avatar
v-lili6-msft
Community Support
6 years ago

HI  Anonymous 

I have adjusted the formula as below:

NEW Pallets to Get 2= 
VAR varQuantityNeeded =
MAX( 'Table A'[Pcs to complete request] )
VAR varCurrentItem =
MAX( 'Table A'[Item] )
----------------------------------------------
var _table1= ADDCOLUMNS(
FILTER(
'Table B',
'Table B'[Item] = varCurrentItem
),
"rank1", 
VAR varCurrentPalletID =
CALCULATE(
MAX( 'Table B'[Pallet ID] )
)
VAR varCurrentPalletPCS =
CALCULATE(
MAX( 'Table B'[Pallet Pcs] )
)
RETURN
 RANKX(
 FILTER(
 'Table B',
 'Table B'[Item] = varCurrentItem
 ),[Pallet Pcs],,DESC,Dense)+
RANKX(
FILTER(
'Table B',
'Table B'[Item] = varCurrentItem
),[Pallet ID],,ASC,Dense)/COUNTROWS('Table B')
)
return
-------------------------------
VAR varPalletCumulative =
ADDCOLUMNS(_table1,"Cumulative Pcs",SUMX(FILTER(_table1,[rank1]<=EARLIER([rank1])),[Pallet Pcs]))
--------------------------------------
VAR varLastrank =
MINX(
FILTER(
varPalletCumulative,
[Cumulative Pcs] >= varQuantityNeeded
),
[rank1]
)
VAR varFinalTable =
FILTER(
varPalletCumulative,
[rank1] <= varLastrank
)
VAR Result =
CONCATENATEX(
varFinalTable,
"Pallet " & [Pallet ID] & ": " & [Pallet Pcs] & "pcs",
","
& UNICHAR( 10 )
)
RETURN
Result

Result:

 

and here is sample pbix file, please try it.

 

Regards,

Lin

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi v-lili6-msft ,
Is possible to do this double sosrt but n this M code?
I hope U can help me again...

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"