Forum Discussion
Error Made Again Measure
Hi,
My measure, [Error Made Again] isn’t working for the below.
Context:
We have employees that are making an error, ‘Data’[Error] in their work on a date, ‘Data’[Error Date], which is caught and addressed to them in their review, another date ‘Data’[Review Period]’.
I am wanting my measure to note where an employee has then made the SAME error AGAIN after the earliest ‘Data’[Review Period] applicable to that 'Data'[Error].
For example:
Error Date | Review Period | Person | Error | Error Made Again |
06/01/2020 | 01/02/2020 | Person A | 1111 | 0 |
15/03/2020 | 01/05/2020 | Person A | 1111 | 1 |
14/04/2020 | 01/05/2020 | Person A | 2222 | 0 |
| 01/05/2020 | Person A | 3333 | 0 |
05/07/2020 | 01/09/2020 | Person A | 1111 | 1 |
23/08/2020 | 01/09/2020 | Person A | 2222 | 1 |
Please can someone assist with this?
Much appreciated!
Dan.
Hi Anonymous ,
Not sure I've understood all your prerequisites, but from what I've got:
#Error made again would be 1 if:
- it is the same error and was made later then the first time (Review Period is checked)
- Error Date is not empty
Measure:
#Error Made Again = var currentError = SELECTEDVALUE('T'[Error]) var currentPerson = SELECTEDVALUE('T'[Person]) var currentReviewPeriod = SELECTEDVALUE('T'[Review Period]) var currentErrorDate = SELECTEDVALUE('T'[Error Date]) var firstReviewPeriod = MINX(FILTER(ALL('T'[Person],'T'[Error],'T'[Review Period]), 'T'[Person] = currentPerson && 'T'[Error] = currentError), 'T'[Review Period]) var result = IF(currentErrorDate = BLANK(), 0, CALCULATE( COUNT('T'[Error]), 'T'[Person] = currentPerson, 'T'[Error] = currentError, 'T'[Review Period] > firstReviewPeriod )) return COALESCE(result,0)If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
Anonymous Hard to say. I actually mocked this up with the sample data provided and it seems to return the correct results that you want. So, if it isn't working at scale, then there must be some boundary cases that aren't represented in the sample data you provided. So, would need to have more data to figure out what is going on. Can you share your PBIX? I have attached the PBIX I created to test this below sig.
10 Replies
Anonymous , Try a new column like
New column =
var _max = maxx(filter(Table, [Review Period] <earlier([Review Period]) && [Person] =earlier([Person])) , [Review Period])
var _error = maxx(filter(Table, [Review Period] =_max && [Person] =earlier([Person])) , [Error])
return
if([Error]=_error,1,0)amitchandak thanks for your attempt but as stated above, it is coming up with '1's despite some of the 'Error Date' columns having BLANKs.
Please let me know if there's anything I need to clarify as this solution would really help to crack this nut I've been struggling with for some days now!
Thanks,
Dan.
Anonymous Kind of like the MTBF calculation: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])
VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
__Current - __PreviousLet's see if I can adapt the approach:
Error Made Again Column = VAR __Person = [Person] VAR __CurrentReview = [Review Period] VAR __CurrentError = [Error] VAR __PreviousReview = MAXX(FILTER('Table',[Review Period]<__CurrentReview),[Review Period]) VAR __PreviousErrors = DISTINCT( SELECTCOLUMNMS( FILTER('Table',[Review Period] = __PreviousReview), "Error",'Table'[Error]) ) RETURN IF(__CurrentError IN __PreviousErrors),1,0)Greg_Deckler thanks for this attempt but it doesn't work as it's bringing back '1' despite 'Error Date' column being BLANK in some instances.
The definition of 'Error Made Again' is if:
There is a date present the 'Error Date' column, and they have been told via 'Review Period', then there is a date in 'Error Date' after the earliest 'Review Period' date relevant to that error.
e.g:
Person A made Error 1111 on the 09/03/2020 evident by the date being in the 'Error Date' column, they have been told via 'Review Period' column with the date 01/04/2020.
Person A makes Error 1111 AGAIN after the earliest 'Review Period', in this example after 01/04/2020. There is another date in 'Error Date' column, 18/05/2020 against Error 1111. (We can see they've been told again via 'Review Period' date 01/06/2020 but that isn't relevant to how we're matching the criteria of what is an error)
Also can I confirm that the way to come to this solution is via a column and NOT a measure then?
Thanks for your continued help on this Greg!
Dan.
Anonymous Seems like you just need to filter out blanks then:
Error Made Again Column = VAR __Person = [Person] VAR __CurrentReview = [Review Period] VAR __CurrentError = [Error] VAR __PreviousReview = MAXX(FILTER('Table',[Review Period]<__CurrentReview && [Person] = __Person && NOT(ISBLANK([Error Date]))),[Review Period]) VAR __PreviousErrors = DISTINCT( SELECTCOLUMNMS( FILTER('Table',[Review Period] = __PreviousReview), "Error",'Table'[Error]) ) RETURN IF(__CurrentError IN __PreviousErrors),1,0)
Hi Anonymous ,
Not sure I've understood all your prerequisites, but from what I've got:
#Error made again would be 1 if:
- it is the same error and was made later then the first time (Review Period is checked)
- Error Date is not empty
Measure:
#Error Made Again = var currentError = SELECTEDVALUE('T'[Error]) var currentPerson = SELECTEDVALUE('T'[Person]) var currentReviewPeriod = SELECTEDVALUE('T'[Review Period]) var currentErrorDate = SELECTEDVALUE('T'[Error Date]) var firstReviewPeriod = MINX(FILTER(ALL('T'[Person],'T'[Error],'T'[Review Period]), 'T'[Person] = currentPerson && 'T'[Error] = currentError), 'T'[Review Period]) var result = IF(currentErrorDate = BLANK(), 0, CALCULATE( COUNT('T'[Error]), 'T'[Person] = currentPerson, 'T'[Error] = currentError, 'T'[Review Period] > firstReviewPeriod )) return COALESCE(result,0)If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
ERD wonderful, that's it thanks!
