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 someone help me how to find the amount of wrong numbers in the column?


Thank you very much

 

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!

     

5 Replies

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

       

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    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!

     

    • PaulW94's avatar
      PaulW94
      Frequent Visitor
      6 years ago

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

    • PaulW94's avatar
      PaulW94
      Frequent Visitor
      6 years ago

      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