Interval calculation

[AilleryO]

Hi,

I am with a problem that should be easy, but I'm stuck...

My data are like this :

My need is to check if the number in the start column is between the start and end numbers of the other lines.

My expected result is on the column in red. The two lines that are not OK is because the numbers with yellow or orange background are included in the interval (between start and end) of one line.

I've been trying with something like this :

IF('Table'[Start]<SELECTEDVALUE('Table'[End])&&

'Table'[Start]>SELECTEDVALUE('Table'[Start]),

"Not OK",

"OK")

But it doesn't work 😞

Any help would be appreciated, thanks.

[Anonymous]

@AilleryO It's not clear with the condition whether the result should be 'OK' or 'Not OK'. Can you elaborate on the condition?

[AilleryO]

Hi,

Thank you for your reply.

I consider line 1 not OK because 8850 (START value on line 1) is between
8746 (START Value on line 2) and 8900 (END value on line 2).

Line 8 is not OK because 8400 (START value on line 8 )is between 8300 (START value on line 7) and 8410 (END value on line 7).

In fact I want to check if the number I find in column START, is included in an interval of any other line.

If the value of the Start column is included in any interval (btwn start and end) of any other line, I want a warning.

Hope it's more clear...

[Anonymous · Accepted solution]

@AilleryO Try this:

Warning_calc_column=var CurrentIndex='Table'[Index]

var CurrentStart='Table'[Start]

var TempTable=FILTER(ALL('Table'),'Table'[Index]<>CurrentIndex&&'Table'[Start]<CurrentStart&&'Table'[End]>CurrentStart)

return IF(COUNTROWS(TempTable)>=1,"Not OK",BLANK())

[AilleryO]

Thanks guy it works like a charm !