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
Im relatively new in PowerBi and Im looking for some advice on how to solve a problem with my data
I have a table where represent the cycle time to each machine. I need to know the machine who has the highest cycle for a specific product so I can show only this value as my constrain in the process flow
My table is like this
Product | Side | Process | Mach | CT |
Module A | Top | SMT | Ln1 | 10 |
Module A | Bottom | SMT | Ln2 | 12 |
Module A | SL | WSM1 | 15 | |
Module A | FA | FA1 | 20 | |
Module B | Top | SMT | Ln1 | 5 |
Module B | SL | WSM1 | 9 | |
Module B | FA | FA1 | 12 |
For Module A, the Constrain is SMT because there are two machines that need to process the product (top side and then Bottom side), therefore, the Total Cycle for the process is 22
For Module B, FA is the constrain since SMT only have one machine
My approach was to count the number of machines based on the product and the process to assure that I have the Total cycle Time and then evaluate the max cycle based on the product, process and this Total cycle,....But Im really lost with DAX functions
Any idea?
Thank you @Greg_Deckler
Maybe Im doing something wrong...This is what I did
My Columns:
[Product] [SIDE] [PROCESS] [MACH] [CT]
According with the proposal
Measure =
// I assume that __Table, __TotalCT and__Max are the variables name
// I believe that 'Table' is the name of the table where this data resides. For me this table's name is CAP_TABLE
VAR __Table = SUMMARIZE('CAP_TABLE',[Process],"__TotalCT ",SUM('CAP_TABLE'[CT])
VAR __Max = MAXX(__Table ,[__TotalCT ])
RETURN
MAXX(FILTER(__Table ,[__TotalCT ]=__Max ),[Process])
This is my logic (maybe wrong)
__TotalCT should be the MAX of [CT] for each [PROCESS] where [Product] is the same and [MACH] is different
ej
For Module A, the product has two sides, each run in different machines (SIDE 1 in Ln1 = 10 sec and SIDE 2 in Ln2=12). Since [MACH] are different the PROCESS output is 12 sec ( the machines can run at the same time, so the output will be based on the highest CT)
Once I have the Process CT, I need to compare with the rest of the PROCESS CT and the highest will be the constrain (in this case is FA=20)
I hope that this make sense to you
Thank you for your help!
@fzn4hs I think I messed up and put the wrong column in for the summarize:
Measure =
// Assumes you have [Product] column in your visual
VAR __Table = SUMMARIZE('Table',[Mach],"__TotalCT",SUM('Table'[CT])
VAR __Max = MAXX(__Table,[__TotalCT])
RETURN
MAXX(FILTER(__Table,[__TotalCT]=__Max),[Process])
Should be a summarize by Mach, not Process.
It does not recognize
[__TotalCT]
Is this another column that I need to calculate? Or should be calculated in the same measure?
@fzn4hs Maybe:
Measure =
// Assumes you have [Product] column in your visual
VAR __Table = SUMMARIZE('Table',[Process],"__TotalCT",SUM('Table'[CT])
VAR __Max = MAXX(__Table,[__TotalCT])
RETURN
MAXX(FILTER(__Table,[__TotalCT]=__Max),[Process])
It's basically Lookup Min/Max. Lookup Min/Max - Microsoft Power BI Community
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 |
---|---|
43 | |
21 | |
20 | |
15 | |
13 |
User | Count |
---|---|
45 | |
41 | |
39 | |
19 | |
19 |