Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
tyxanu
Helper I
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.

tyxanu_0-1670330095100.png

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
Bifinity_75
Solution Sage
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:

Bifinity_75_0-1670331502760.png

 

Best regards

 

View solution in original post

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

View solution in original post

Bifinity_75
Solution Sage
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:

Bifinity_75_0-1670438269319.png

 

Best Regards!

 

 

View solution in original post

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:

Bifinity_75_0-1670612523256.png

 

Best Regards

 

View solution in original post

Bifinity_75
Solution Sage
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:

Bifinity_75_0-1671481367941.png

 

Best Regards!

 

 

View solution in original post

9 REPLIES 9
Bifinity_75
Solution Sage
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:

Bifinity_75_0-1671481367941.png

 

Best Regards!

 

 

Bifinity_75
Solution Sage
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:

Bifinity_75_0-1671220198058.png

 

Best Regards

 

 

tyxanu
Helper I
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 :

tyxanu_3-1671186788393.png

Thank you!

 

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

Bifinity_75
Solution Sage
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:

Bifinity_75_0-1670438269319.png

 

Best Regards!

 

 

@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

tyxanu_0-1670597946876.png

 

 

 

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:

Bifinity_75_0-1670612523256.png

 

Best Regards

 

Bifinity_75
Solution Sage
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:

Bifinity_75_0-1670331502760.png

 

Best regards

 

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.