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

AllisonKennedy's avatar
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 )
)