Forum Discussion
Obtain maximum values according to the information
Hello good morning.
Could you please help me with the following in Power Bi?
Table 1 is where I want the information to be shown and table two is where the data is obtained.
For example:
In the OP Fecha column, I want the "Oper" to appear according to the most recent date, for example, the Oper indicated in green is the one that should be shown. Depends of the MO (Work Order)
In the OP MFG column I want it to show me the major Oper that has a 1 in the MFG column. In this example it should be the operation "30"
In the OP MAX column, only show me the OP with the largest number. Depends on the Manufacturing Order, i have a lot of datos, it is only for the example In this case the las operation is "50"
Could you please help me with the formulas to obtein that information?
Thanks
Hello! I created a second set of data with another work order, just so we can see that the measures work as expected with more than one work order:
For Op Fecha you can use below:
CALCULATE (MAXX (VALUES('Table'[Work Order]),CALCULATE (MAX('Table'[Oper]),'Table'[End Date] = MAX('Table'[End Date]))))For Op Mfg use below:CALCULATE (MAXX (VALUES('Table'[Work Order]),CALCULATE (MAX('Table'[Oper]),'Table'[Mfg] = 1)))For Op Max use below:CALCULATE (MAXX (VALUES('Table'[Work Order]),MAX('Table'[Oper])))Here are the results:The items you have highlighted in green are not the max time. For the one ending 314 it is the value of 40. For the one ending 396, it is 90. For the table visual you are making you have to remove manufacturing order and replace it with the work order column from your excel table (same one referenced in the measures).
4 Replies
All of these formulae require pretty similar building:
OP Fecha = CALCULATE(MAX('YourTable'[Oper]), FILTER(ALL('YourTable'),
[Work Order] = SELECTEDVALUE('YourTable'[Work Order])
&& [End Time] = MAX('YourTable'[End Time])
)
OP MFG =
= CALCULATE(MAX('YourTable'[Oper]), FILTER(ALL('YourTable'),
[Work Order] = SELECTEDVALUE('YourTable'[Work Order])
&& [MFG] = 1)
)
OP Max =
= CALCULATE(MAX('YourTable'[Oper]), FILTER(ALL('YourTable'),
[Work Order] = SELECTEDVALUE('YourTable'[Work Order])
)
)
This amount of code isn't really necessary if your table only has one row per work order (this will still work for that but it is a bit over engineered for that case) if you do only have one row per work order in your table (probably a matrix visual would be better in this case) you could simplify to:
OP Fecha =
VAR _max_date = MAX('YourTable'[End Time])
RETURN
CALCULATE(MAX('YourTable'[Oper]), 'YourTable'[End Time] = _max_date)
OP MFG = CALCULATE(MAX('YourTable'[Oper]), 'YourTable'[MFG] = 1)
OP Max = MAX('YourTable'[Oper])
The reason why these measures can be so light is because a matrix aggregates based on the row and column context so each row within the matrix is effectively an aggregated view of each individual works order, so the operations don't need to manually remove the row context then reapply it slightly differently like in the above case with a table. Hope this helps, let me know if you have any issues or questions.
- audreygerred
Super User
Hello! I created a second set of data with another work order, just so we can see that the measures work as expected with more than one work order:
For Op Fecha you can use below:
CALCULATE (MAXX (VALUES('Table'[Work Order]),CALCULATE (MAX('Table'[Oper]),'Table'[End Date] = MAX('Table'[End Date]))))For Op Mfg use below:CALCULATE (MAXX (VALUES('Table'[Work Order]),CALCULATE (MAX('Table'[Oper]),'Table'[Mfg] = 1)))For Op Max use below:CALCULATE (MAXX (VALUES('Table'[Work Order]),MAX('Table'[Oper])))Here are the results:Do you know what i'm doing bad? I did the example that you did but only show the max information not depends the other works orders.
The table with the information is named Excel and the table where i want to see the information is MasterTable
- audreygerred
The items you have highlighted in green are not the max time. For the one ending 314 it is the value of 40. For the one ending 396, it is 90. For the table visual you are making you have to remove manufacturing order and replace it with the work order column from your excel table (same one referenced in the measures).
