Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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.
Solved! Go to Solution.
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
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
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"
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:
MEASURE:
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |