How do I create a complicated aggregated measure in Power Bi for use on a dashboard?

I have a table that is Imported from MS SQL. The table data gets refreshed when the PBIX file is opened. The minimalist data looks like this:

• For context, the data represents quality assurance tests run on some equipment that we manufacture. When a part is tested, we perform several automated tests, and the data gets written to a table similar to what you see below. So think of "WorkOrder" as a PartNumber, TaskID would be how many times this particular part was tested. The TestType is the type of test that gets executed.

TestData table:

What I need to calculate is the 1stPassYield and the TotalPassYield.

So, how do I do all that and then create a Card on a dashboard showing each of those percentages? Ultimately, I'd like to be able to slice the data based on WorkOrder and have that Card percentage updated based on the data. My data has many WorkOrders and each WorkOrder should have its own 1stPassYield and TotalPassYield values (or collectively as a whole)

Thoughts on how to accomplish that? I tried creating one that transforms the data the way I needed using PowerQuery, but I just couldn't get it to function the way it should. What am I missing? Any pointers would be most appreciated.

1stPassYield is calculated as:

• If you group the data by the WorkOrder and TaskID, take the lowest boolean (0 or 1) from PassFail. That SHOULD give you 2 Rows similar to:

• Given the above, that means WorkOrder 123 has a 1stPassYield of 50%

TotalPassYield assumes that as long as an entire WorkOrder/TaskID grouping cis "Pass", then that's 100% pass. As such, the simulated data shown above should provide the following values for the measures I need:

• 1stPassYield: 50%
• TotalPassYield: 100%

I suppose you could group the above grouped table on WorkOrder taking the max value of PassFail (which should be Pass).

So the question is, how do I accomplish this feat and display the data for each measure on a Card on a dashboard in Power BI? I attempted to use PowerQuery to build it out, but I could not get things together the way that they needed to be and, therefore, was unable to get the percentage measure created. Additionally, I'd like to be able to slice the data based on WorkOrder so that the Card is updated for the WorkOrders that are selected. So if one work order is selected, then I'll get 1stPassYield and TotalPassYield for that single WorkOrder. If I select multiple, then average those 2 measures together and present that figure.

Any assistance you could provide would be most helpful. Thanks.

Best to accomplish this with DAX and Measures (not Power Query).

Your Measures could look like:

1stPassYield = 
  var tbl = SUMMARIZE( TestData, [WorkOrder], [TaskID], "Min P/F", MIN([PassFail]) )
  var result = 
    DIVIDE( 
      COUNTROWS(FILTER(tbl, [Min P/F] = "Pass")),
      COUNTROWS(tbl)
    )
  return result

TotalPassYield = 
  var tbl = SUMMARIZE( TestData, [WorkOrder], [TaskID], "Min P/F", MIN([PassFail]) )
  var tbl2 = SUMMARIZE( tbl, [WorkOrder], "Max P/F", MAXX(tbl, [Min P/F]) )
  var result = 
    DIVIDE( 
      COUNTROWS(FILTER(tbl2, [Max P/F] = "Pass")),
      COUNTROWS(tbl2)
    )
  return result