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( '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] ))RETURNFILTER('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 ))RETURNResultBut now, I need a little bit more help. How can I edit this measure to do a Sort into the previous Sort? Let me explain you with an example.Table A;temRequestOnSite PcsOffSite PcsPcs to complete request609048-109-1012020160100Table B;ItemPallet IDPallet Pcs609048-109-10710609048-109-101040609048-109-101220609048-109-101320609048-109-10920609048-109-101140609048-109-10810 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 only the Pallets 10, 11 & 9, this pallets sum 100 pcs.ItemPallet IDPallet Pcs609048-109-101040609048-109-101140609048-109-10920609048-109-101220609048-109-101320609048-109-10710609048-109-10810 Is it possible?I hope you can help me again edhans / community...Have a good day!***The blue color in the tables doesn't have any relation with the blue text of the Measure, it's just for can diference the columns.

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( '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

But now, I need a little bit more help. How can I edit this measure to do a Sort into the previous Sort?
Let me explain you with an example.

Table A;

tem	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 only the Pallets 10, 11 & 9, this pallets sum 100 pcs.

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

Is it possible?
I hope you can help me again edhans / community...
Have a good day!

***The blue color in the tables doesn't have any relation with the blue text of the Measure, it's just for can diference the columns.

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

Double Sort or Sort into another previous Sort.pbix36 KB

4 Replies

AllisonKennedy
Community Champion
6 years ago
I have edited the measure a bit to use syntax or functions that make more sense to me (SELECTEDVALUE instead of MAX in some places) and to show you how the sort is happening I have put that in as it's own CALCULATED COLUMN inside the table. Depending on how many items you have, this may be less efficient in your data model, so you can combine them back into one measure if needed once you understand how it works.

COLUMNS:

_ColSortOrder =
VAR CurrentItem = TableB[Item]
RETURN
RANKX(FILTER(TableB, CurrentItem=TableB[Item]),
TableB[Pallet Pcs]&TableB[Pallet ID],,DESC,Dense)

_CumulativePcs =
VAR CurrentSortOrder =
TableB[_ColSortOrder]
VAR _CurrentItem = TableB[Item]
RETURN
SUMX(FILTER(TableB,TableB[_colSortOrder]<=CurrentSortOrder && TableB[Item]=_CurrentItem), TableB[Pallet Pcs])

MEASURE:
Pallets to Get 2 =
VAR varQuantityNeeded =
SELECTEDVALUE( 'TableA'[Pcs to complete request] )
VAR varCurrentItem =
SELECTEDVALUE( 'TableA'[Item] )
VAR varLastPalletNeeded =
MINX(
FILTER(
TableB,
TableB[_CumulativePcs] >= varQuantityNeeded
),
TableB[_colSortOrder]
)
VAR varFinalTable =
FILTER(
TableB,
TableB[_colSortOrder] <= varLastPalletNeeded
)
RETURN
CONCATENATEX(
varFinalTable,
"Pallet " & [Pallet ID] & ": " & [Pallet Pcs] & "pcs",
","
& UNICHAR( 10 )
)

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

Double Sort or Sort into another previous Sort.pbix36 KB

Anonymous
Not applicable
6 years ago
Thank u so muchv-lili6-msft , it works just for what i need.

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"

Forum Discussion

Double Sort or Sort into another previous Sort ?

4 Replies

Related content

Count function is doubling

Double axis line

Double layered Pie chart

Double Heading Matrix

Previous Year Measure for Line Chart