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,
It's the second post I do for this. I'm very obfuscated 'cause I can't solve this yet...
I hope somebody can help me.
I have two tables;
Table A
Item | Request | OnSite Pcs | OffSite Pcs | Pcs to complete request |
609048-109-10 | 455 | 230 | 630 | 225 |
Table B
Item | Pallet ID | Pallet Pcs |
8 | 72 | |
609048-109-10 | 21 | 132 |
609048-109-10 | 33 | 102 |
609048-109-10 | 34 | 132 |
609048-109-10 | 86 | 132 |
609048-109-10 | 97 | 60 |
Table B is the OffSite Pcs of table A, but it's splitted in Pallets.
If I order the Pallets ID ascending or descending, I need to create a Table or Matrix that will shows me only the Pallets that adds up enough pcs to complete the Requested Pcs.
A Table like this;
Item | Pallet ID | Pallet Pcs |
609048-109-10 | 8 | 72 |
609048-109-10 | 21 | 132 |
609048-109-10 | 33 | 102 |
Or a Matrix like this;
Item | Pallet ID | Pallet Qty |
609048-109-10 | ||
8 | 72 | |
21 | 132 | |
33 | 102 |
In this case, the Table or Matrix bring me the Pallets 8, 21, and 33, because the total Pcs of the three pallets is 306, and i need, 225 Pcs in table A to complete the Request
***(The colors on the tables are only to separate the data fields)
I've been trying for days and I doesn't achieve to think how to create this Table or Matrix.
I hope somebady can help me soon.
Solved! Go to Solution.
See if this works for you. It returns a text box (scalar, or single, value) that is a field for a table.
This is the code behind the "Pallets to Get" measure.
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
The varFinalTable variable (next to the last variable) returns this table in memory:
So the trick is how to get that into a visual. A measure must be a scalar value, and I cannot think of a good way to do it otherwise. I could create a calculated table, but that is on a per item basis.
I might redo this in Power Query that will return similar tables that are loaded, but this will get you something at least. And if you have a lot of data, the Power Query solution would be really slow. I will have to tinker with it.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingSee if this works for you. It returns a text box (scalar, or single, value) that is a field for a table.
This is the code behind the "Pallets to Get" measure.
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
The varFinalTable variable (next to the last variable) returns this table in memory:
So the trick is how to get that into a visual. A measure must be a scalar value, and I cannot think of a good way to do it otherwise. I could create a calculated table, but that is on a per item basis.
I might redo this in Power Query that will return similar tables that are loaded, but this will get you something at least. And if you have a lot of data, the Power Query solution would be really slow. I will have to tinker with it.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThank you so much, it was very helpful to me.
Both answers give me that I need, but such as you said, the first solution works better because my data base is to big.
Im very grateful.
Great @Anonymous - glad one of the solutions worked for you.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @edhans
I have another question, I hope you can help me again.
Is possible to edit the Measure that you give me, to do a "double sort"? A Sort, into the previous sort.
Let me show you an example.
Table A;
Item | 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 the Pallets 10, 11 & 9, this pallets sum 100 pcs.
Bring less pallets is such a better thing in financial and operative way.
Is it possible?
I hope you can help me again... Have a good day!
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 |
Ok @Anonymous - here is the Power Query version. It returns a hastily prepared matrix that looks like this:
I created Table C, which was your Table B, but modified. The M code to do this is as follows:
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"
Before filtering and removing columns, it returns this table in Power Query:
I want to keep the three rows in red. The next two steps just filter for that, then keeps only the first 3 columns.
I did the Enough Pieces and Pallets Needed in two columns because it is Friday afternoon and getting all of that logic in one column was making my head hurt this late in the week. 😁
I make no warrants however on the performance of the M code on a large (100K or larger) record set. And I hate the matrix I created, but now that you have a table to link to, you can format it or display it however you like.
Here is the PBIX file.
Please mark one or both as the solution if they help. Or tell me I am off my rocker and totally missed your objective!
To adapt the M code to your model...
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @edhans ,
It's me again, sorry for ask a lot...
With more help from the community, I modified a little bit the measure that you gave to me, to sort first by Pallet Pcs, and then by Pallet ID.
But how can I do it into the M code? Is it possible?
Let me show you...
The measure just sorting by Pallet ID is the column "Pallets to Get", but the measure sorting first by Pallet Pcs, and then, by Pallet ID brings to me less Pallets, is the column "New Pallets to Get 2"
Is possible to do this double sort but in the M code that you gave me?
I hope you can help me again and for the last time, LOL.
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 |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |