cancel
Showing results for
Did you mean:
New Member

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

2 ACCEPTED SOLUTIONS
Solution Sage

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.

Super User IV

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

---------------------------------------

@ me in replies or I'll lose your thread!!!

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

10 REPLIES 10
Solution Sage

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.

New Member

@ERD wonderful, that's it thanks!

Super User IV

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

---------------------------------------

@ me in replies or I'll lose your thread!!!

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

New Member

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

Super User IV

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

---------------------------------------

@ me in replies or I'll lose your thread!!!

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

New Member

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

Super User IV

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

---------------------------------------

@ me in replies or I'll lose your thread!!!

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

New Member

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

Super User IV

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

New Member

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

Announcements

#### Manage your user group events

Check out the News & Announcements to learn more.

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

#### Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

#### Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors
Top Kudoed Authors