Mark duplicate combined data in relation to another criteria

[tyxanu]

Hello,

Can anyone help with a PBi DAX that will mark the following lines as follows?

I need to flag or mark(in column D) each combination of "Number" and "Occurence" that has double entries: one with "N" Flag and one with "Y" Flag.

If the combination of "Number" and "Occurence" has only 1 entry with "N" Flag, do nothing/flag nothing (PS: It will never happen to have only "Y" flag).

In the end, I need to be able to filter out the data that has this mark.

Thank you!

[Bifinity_75 · Accepted solution]

Hi @tyxanu , try this calculate column:

Target = var rep=CALCULATE(COUNT('Table'[Number]),ALLEXCEPT('Table','Table'[Number]))
var flag_p=CALCULATE(max('Table'[Flag]),ALLEXCEPT('Table','Table'[Number]))
return
if(flag_p="Y" && rep>1,"Mark this","")

The result:

Best regards

[tyxanu · Accepted solution]

@Bifinity_75 , can you also share a logic that would help me mark all the entries that contain duplicate combination of "Number" and "Occurence" solely? (not considering "Flag")

[Bifinity_75 · Accepted solution]

Hi @tyxanu , can you try this:

- Create this calculate column:

Numb_Ocurr = 'Table'[Number] & 'Table'[Ocurrence]

- Create this another calculate column:

Result = var previous= CALCULATE(COUNT('Table'[Numb_Ocurr]),ALLEXCEPT('Table','Table'[Numb_Ocurr]))
return
if (previous=1,"","Mark this")

The result:

Best Regards!

[Bifinity_75 · Accepted solution]

Hi @tyxanu , create this calculate column:

Target = var rep=CALCULATE(COUNT('Table'[Numb_Ocurr]),ALLEXCEPT('Table','Table'[Numb_Ocurr]))
return
if(CONTAINSSTRING('Table'[Flag],"Yes") && rep>1,"Mark this","") 

The result:

Best Regards

[Bifinity_75 · Accepted solution]

Hi @tyxanu, pretty much reducing the logic you've shown me, try with this calculate column:

Target = 
var dupl_comb=CALCULATE(count('Table'[Number]),ALLEXCEPT('Table','Table'[Number],'Table'[Ocurrence]))
return
if('Table'[Flag 1]="N" && 'Table'[Flag 2]="A" || dupl_comb=1 && 'Table'[Flag 1]="N","","Mark this")

The result:

Best Regards!

[Bifinity_75]

Hi @tyxanu , try this calculate column:

Target2 = IF (CALCULATE(COUNT('Table'[Numb_Ocurr]),
ALLEXCEPT('Table','Table'[Numb_Ocurr]))>1,"Mark this","")

The result:

Best Regards

[tyxanu]

@Bifinity_75 , can you please help me review this again? Seems that I have not captured all the possible scenarios.

a) for each duplicate combination of "Number" and "Occurrence" that has different Flag1= "Y" and "N" -> "Mark This"

b) for each duplicate combination of "Number" and "Occurrence" that has only Flag1="N":

     check if Flag2 = BLANK and if yes -> "Mark This", otherwise

               if Flag2 = "A" -> do nothing

c) for each duplicate combination of "Number" and "Occurrence" that has only Flag1="Y" -> "Mark This"

d) for each unique combinations(no duplicate) of "Number" and "Occurrence":

    if Flag1 = "Y" -> "Mark This", otherwise

    if Flag1 = "N" -> do nothing

I have highlighted the scenarios and the desired outcome below :

Thank you!

[tyxanu]

@Bifinity_75 , can I get your precious help over this new issue? 

[Bifinity_75 · Accepted solution]

Hi @tyxanu , can you try this:

- Create this calculate column:

Numb_Ocurr = 'Table'[Number] & 'Table'[Ocurrence]

- Create this another calculate column:

Result = var previous= CALCULATE(COUNT('Table'[Numb_Ocurr]),ALLEXCEPT('Table','Table'[Numb_Ocurr]))
return
if (previous=1,"","Mark this")

The result:

Best Regards!

[tyxanu]

@Bifinity_75 , what about this scenario:

Based on the below picture, can you mark every:

- duplicate combination of "Number" and "Occurence" that has a "Flag" that contains the word "Text Yes" 

and

- don't mark any unique combination of "Number" and "Occurence" that has a "Flag" that contains the word "Text Yes" or no "Flag" at all

[Bifinity_75 · Accepted solution]

Hi @tyxanu , create this calculate column:

Target = var rep=CALCULATE(COUNT('Table'[Numb_Ocurr]),ALLEXCEPT('Table','Table'[Numb_Ocurr]))
return
if(CONTAINSSTRING('Table'[Flag],"Yes") && rep>1,"Mark this","") 

The result:

Best Regards

[Bifinity_75 · Accepted solution]

Hi @tyxanu , try this calculate column:

Target = var rep=CALCULATE(COUNT('Table'[Number]),ALLEXCEPT('Table','Table'[Number]))
var flag_p=CALCULATE(max('Table'[Flag]),ALLEXCEPT('Table','Table'[Number]))
return
if(flag_p="Y" && rep>1,"Mark this","")

The result:

Best regards

[tyxanu · Accepted solution]

@Bifinity_75 , can you also share a logic that would help me mark all the entries that contain duplicate combination of "Number" and "Occurence" solely? (not considering "Flag")