Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
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
Thanks a lot for the effort. I used your measure to solve my problem.
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
Thanks for the quick answer. I will check it.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
95 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |