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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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