cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gschalk87 Frequent Visitor
Frequent Visitor

Help on measure formula

Hello All,

 

I am having some trouble figuring out how to make a measure work correctly, I am trying to get the count of final failures in a process.

 

i have data in a table that records part number, serial number, result, pass(65) or fail (105). seems simple enough to use

Final Reject = 
CALCULATE(DISTINCTCOUNT(StationEventHist[PartSerialNbr]),StationEventHist[EventID]=VALUE(105))

however the process is structured where a serial number can fail multiple times for various reasons, get repaired and retested and then get a good result, leaving me counting rejects that were fixed or corrected

 

example:

 

SN     Result

123     fail

123     fail

123     fail

123     pass

'

my formula yeilds final reject = 3 and final accept = 1

The goal is to have final reject count = 0 and final accept count = 1

 

Thank you!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Seward12533 New Contributor
New Contributor

Re: Help on measure formula

Another approach that woudl work even If you don't have the date field suggested by jthomson.

 

You could staging tables of serial numbers that have passed and failed using summarize with filters to capure only passing adn failing NS's respectively and include other calculated or relevant columns as needed,

 

Passing SN = CALCULATETABLE(SUMMARIZE('StationEventHist',StationEventHist[PartSerialNbr]),'StationEventHist[EventID]<>65)

Reject SN = CALCULATETABLE(SUMMARIZE('StationEventHist',StationEventHist[PartSerialNbr]),'StationEventHist[EventID]<>105)

 

Then update you mesures to calculate form these tables and join the tables in your model by SN so any other filters you apply would work correctly.

 

 

4 REPLIES 4
Super User
Super User

Re: Help on measure formula

There's a few ways you could look at this depending on how exactly your data model works - if each SN can only pass once, you could do a distinct count of all the serial numbers, then do a count of the number of rows that have a result of pass and take one from the other, or if there's some sort of date/time field with each row, you could play with filters to just look at the most recent instance for any given SN (should be easy to search for the correct syntax)

Seward12533 New Contributor
New Contributor

Re: Help on measure formula

Another approach that woudl work even If you don't have the date field suggested by jthomson.

 

You could staging tables of serial numbers that have passed and failed using summarize with filters to capure only passing adn failing NS's respectively and include other calculated or relevant columns as needed,

 

Passing SN = CALCULATETABLE(SUMMARIZE('StationEventHist',StationEventHist[PartSerialNbr]),'StationEventHist[EventID]<>65)

Reject SN = CALCULATETABLE(SUMMARIZE('StationEventHist',StationEventHist[PartSerialNbr]),'StationEventHist[EventID]<>105)

 

Then update you mesures to calculate form these tables and join the tables in your model by SN so any other filters you apply would work correctly.

 

 

Community Support Team
Community Support Team

Re: Help on measure formula

Hi @Gschalk87,

 


Can you mark the proper answer as a solution please?

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Gschalk87 Frequent Visitor
Frequent Visitor

Re: Help on measure formula

sorry for delay! ended up digging into the current SQL Statement flow and folling its logic and the idea suggested, if it dosnt have a good test record, it must have a bad record. this matches the current system, but i may look into running stored procedure to use the exact method everyone on site is used to seeing.

 

Thank you for your help!