Forum Discussion
Error Made Again Measure
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.
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)
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)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.
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.