cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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;

temRequestOnSite PcsOffSite PcsPcs to complete request
609048-109-1012020160100

Table B;

ItemPallet IDPallet Pcs
609048-109-10710
609048-109-101040
609048-109-101220
609048-109-101320
609048-109-10920
609048-109-101140
609048-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 Pcs
609048-109-101040
609048-109-101140
609048-109-10920
609048-109-101220
609048-109-101320
609048-109-10710
609048-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.


1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Double Sort or Sort into another previous Sort ?

HI  @javiermontess 

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:

4.JPG

 

and here is sample pbix file, please try it.

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Highlighted
Community Champion
Community Champion

Re: Double Sort or Sort into another previous Sort ?

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 )
)

 


______________


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


https://sites.google.com/site/allisonkennedycv

Highlighted
Community Support
Community Support

Re: Double Sort or Sort into another previous Sort ?

HI  @javiermontess 

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:

4.JPG

 

and here is sample pbix file, please try it.

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Helper I
Helper I

Re: Double Sort or Sort into another previous Sort ?

Thank u so much@v-lili6-msft , it works just for what i need.

Highlighted
Helper I
Helper I

Re: Double Sort or Sort into another previous Sort ?

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"

 

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors