cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## Mark duplicate combined data in relation to another criteria

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!

5 ACCEPTED SOLUTIONS
Solution Sage

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

Helper I

@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")

Solution Sage

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!

Solution Sage

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

Solution Sage

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!

9 REPLIES 9
Solution Sage

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!

Solution Sage

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

Helper I

@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!

Helper I

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

Solution Sage

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!

Helper I

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

Solution Sage

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

Solution Sage

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

Helper I

@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")

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors