Error Made Again Measure

[PowerBIDan]

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.

[ERD · Accepted solution]

Hi @PowerBIDan ,

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 · Accepted solution]

Hi @PowerBIDan ,

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.

[PowerBIDan]

@ERD wonderful, that's it thanks!

[Greg_Deckler]

@PowerBIDan Kind of like the MTBF calculation: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
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 - __Previous

Let'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)

[PowerBIDan]

@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.

[Greg_Deckler]

@PowerBIDan 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)

[PowerBIDan]

@Greg_Deckler unfortunately this still isn't working, BLANKS are still in the 'Error Date' column and I can't figure out why!

My data has 20,000 rows and only 915 have 'Error Date' NOT BLANK, yet your column solution is still filtering with BLANKS in the 'Error Date' column; 9000/20,000 are still apparent when I filter 'Error Made Again' column to '1's values only.

Any idea why this may be? Please see below how I have edited the column to be applicable to the columns within my data:

Error Made Again Column = 
  VAR __Person = [Fee Earner]
  VAR __CurrentReview = [Review Period]
  VAR __CurrentError = [Code for mapping]
  VAR __PreviousReview = MAXX(FILTER('Casualty Error Data',[Review Period]<__CurrentReview && [Fee Earner] = __Person && NOT(ISBLANK([Error Date]))),[Review Period])
  VAR __PreviousErrors = 
    DISTINCT(
      SELECTCOLUMNS(
        FILTER('Casualty Error Data',[Review Period] = __PreviousReview),
        "Code for mapping",'Casualty Error Data'[Code for mapping])
    )
RETURN
  IF(__CurrentError IN __PreviousErrors,1,0)

[Greg_Deckler · Accepted solution]

@PowerBIDan 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.

[PowerBIDan]

@Greg_Deckler thanks Greg, apologies for the delay in replying, it seems @ERD managed to get the solution. Still not sure why your solution isn't working (and for the record my measure did the same thing) but appreciate your support.

Cheers!

Dan.

[amitchandak]

@PowerBIDan , 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)

[PowerBIDan]

@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.