Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
v-lili6-msft
Community Support
Community Support

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:

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
v-lili6-msft
Community Support
Community Support

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:

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.
Anonymous
Not applicable

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"

 

Anonymous
Not applicable

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

AllisonKennedy
Super User
Super User

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

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as 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 C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.