Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
fzn4hs
Frequent Visitor

Number of Rows based on multiple columns values

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?

 

 

5 REPLIES 5
fzn4hs
Frequent Visitor

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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler 

Could you please help me to understand __TotalCT?

Than you!

JC

It does not recognize 

[__TotalCT]

Is this another column that I need to calculate? Or should be calculated in the same measure?

Greg_Deckler
Super User
Super User

@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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.