Forum Discussion

PaulW94's avatar
PaulW94
Frequent Visitor
6 years ago
Solved

identifying right order

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 some...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    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!

     

vivran22's avatar
vivran22
Community Champion
6 years ago

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's avatar
    PaulW94
    Frequent Visitor
    6 years ago

    Thanks for the quick answer. I will check it.