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!

     

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