cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PowerBIDan
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
ERD
Solution Sage
Solution Sage

Hi @PowerBIDan ,

Not sure I've understood all your prerequisites, but from what I've got:

ERD_0-1621404823596.png

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

View solution in original post

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




View solution in original post

10 REPLIES 10
ERD
Solution Sage
Solution Sage

Hi @PowerBIDan ,

Not sure I've understood all your prerequisites, but from what I've got:

ERD_0-1621404823596.png

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

View solution in original post

@ERD wonderful, that's it thanks!

Greg_Deckler
Super User IV
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!




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

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




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

 

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




View solution in original post

@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
Super User IV
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!

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

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

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

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

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

PowerPlatform 768x460.png

Microsoft Learn

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

Top Solution Authors