cancel
Showing results 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

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

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 Monthly Update - June 2024

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

Fabric Community Update - June 2024

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

New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors