How to Check if group have correlative numbers

[Danielecc]

Hi Everyone,

I am looking for something to check if I group by "Seller" see if they have the correlative numbers or not (could be repeated or not be in order), creating a DAX calculated column called "CHECK".

Something like this:

The data is:

SellerWithCODENumbers

D.Celis	EEUU01	1
D.Celis	EEUU02	1
D.Celis	EEUU03	2
D.Celis	EEUU05	2
J.Perez	EEUU01	1
J.Perez	EEUU02	1
J.Perez	EEUU07	3
J.Perez	EEUU03	2
J.Perez	EEUU05	4
J.Perez	EEUU13	4
J.Perez	EEUU02	5
S.Gomez	EEUU06	1
S.Gomez	EEUU12	1
S.Gomez	EEUU09	3
S.Gomez	EEUU03	2
S.Gomez	EEUU10	2
S.Gomez	EEUU13	5
S.Gomez	EEUU15	6
J.Smith	EEUU01	1
J.Smith	EEUU02	1
J.Smith	EEUU09	3
J.Smith	EEUU03	4
J.Smith	EEUU05	5
J.Smith	EEUU15	6
J.Smith	EEUU07	6
P.Court	EEUU01	1
P.Court	EEUU02	2
P.Court	EEUU07	2
P.Court	EEUU03	4
P.Court	EEUU04	4
P.Court	EEUU14	5
P.Court	EEUU11	5
S.Kent	EEUU06	1
S.Kent	EEUU12	1
S.Kent	EEUU09	3
S.Kent	EEUU03	2
S.Kent	EEUU08	2
S.Kent	EEUU14	4
S.Kent	EEUU11	4
P.Mcay	EEUU01	1
P.Mcay	EEUU02	1
P.Mcay	EEUU07	2
P.Mcay	EEUU04	5
P.Mcay	EEUU05	7

Thanks a lot and best regards...

[CNENFRNL · Accepted solution]

For fun, a solution with Excel worksheet formula,

[CNENFRNL · Accepted solution]

For fun, a solution with Excel worksheet formula,

[Danielecc · Accepted solution]

Hi @CNENFRNL , thanks for your post.

Your code works in my example but for some reason in the real file I have differences with my result.

I used this code and work:

Match =
IF(INFO[Numbers]<>BLANK(),
VAR _MAX = CALCULATE(MAX(INFO[Numbers]),FILTER(INFO,INFO[Numbers]<>BLANK()),ALLEXCEPT(INFO,INFO[Seller]))
VAR _NUM = CALCULATE(DISTINCTCOUNT(INFO[Numbers]), INFO[Numbers]<>BLANK(),ALLEXCEPT(INFO,INFO[Seller]))
VAR _RESULT = IF( (_MAX - _NUM) = 0,"Match",BLANK())
RETURN
_RESULT
)

Your code is ok in the example, then I Accept as Solution. Thank's for your help and best regards.

[AlexisOlson]

Can you clarify what you mean by "correlative numbers"? I don't understand what condition causes OK or FAIL.

[Danielecc]

Hi @AlexisOlson , thanks for see the post.

For example, if you see the Group "J.Perez" have numbers:

1, 1, 3, 2, 4, 4, 5

The min number es 1

The max number is 5

Then the range must be 1, 2, 3, 4, 5

And if you compare the range:  1, 1, 3, 2, 4, 4, 5  <-- this range have all the numbers 1 to 5.

Now a "FAIL" example with the Group "S.Gomez" with numbers:

1, 1, 3, 2, 2, 5, 6

The Min number is 1

The Max number is 6

Then the rangue must be 1, 2, 3, 4, 5, 6

And if you compare the range: 1, 1, 3, 2, 2, 5, 6  <-- in this range the number 4 not exist, for that reason "FAIL" if any number not exist between the min or max, then FAIL.

Sorry for my bad english and I hope this explanation help to understand my post.

Thank's again and best regards...