identifying right order

[PaulW94]

I have a sequence of numbers in a column. I'm trying to find out the amount of cells that don't fit in the sequence.

E.g:
2,3,5,6,8,9,7,10,11,12

In that order the 7 would be wrong.

Can someone help me how to find the amount of wrong numbers in the column?

Thank you very much

[v-kelly-msft · Accepted solution]

Hi @PaulW94 ,

If the numbers are ordered ascending,using the measure below:

Measure = 
var pre=IF(MAX('Table'[Index])<>MINX(ALLSELECTED('Table'),'Table'[Index]),CALCULATE(MAX('Table'[column]),FILTER(ALLSELECTED('Table'),'Table'[Index]=SELECTEDVALUE('Table'[Index])-1)),MAX('Table'[column]))
Return
IF(SELECTEDVALUE('Table'[Index])=MINX(ALLSELECTED('Table'),'Table'[Index])||SELECTEDVALUE('Table'[Index])=MAXX(ALLSELECTED('Table'),'Table'[Index]),MAX('Table'[column]),
IF(SELECTEDVALUE('Table'[column])>pre,SELECTEDVALUE('Table'[column]),"wrong"))

If the numbers are ordered descending,using the measure below:

Measure = 
var _after=IF(MAX('Table'[Index])<>MAXX(ALLSELECTED('Table'),'Table'[Index]),CALCULATE(MAX('Table'[column]),FILTER(ALLSELECTED('Table'),'Table'[Index]=SELECTEDVALUE('Table'[Index])+1)),MAX('Table'[column]))
Return
IF(SELECTEDVALUE('Table'[Index])=MINX(ALLSELECTED('Table'),'Table'[Index])||SELECTEDVALUE('Table'[Index])=MAXX(ALLSELECTED('Table'),'Table'[Index]),MAX('Table'[column]),
IF(SELECTEDVALUE('Table'[column])<_after,SELECTEDVALUE('Table'[column]),"wrong"))

Finally you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly

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

[PaulW94]

Thanks a lot for your answer. Can you help me with a little additional problem. 

Now i want to count all my "wrongs" that are created with the measure. 

Should i change the formula of the measure, or could I create a calculated column or a new measure to count my "wrongs"?

Thank a lot and best regards 

Paul

[PaulW94]

Thanks a lot for the effort. I used your measure to solve my problem.

[vivran22]

Hello @PaulW94 ,

If the logic is to compare the current value with the previous row, then you may use following as a caluclated column:

Column =
VAR PrevValue =
    CALCULATE (
        MAX ( 'Table'[Column1] ),
        FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 )
    )
VAR CurrValue = 'Table'[Column1]
VAR Check =
    IF ( CurrValue < PrevValue, "Wrong" )
RETURN
    Check

For this, I have added an index column using Power Query (Power Query > Add Column > Index Column

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Connect on LinkedIn

[PaulW94]

Thanks for the quick answer. I will check it.