Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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!
Solved! Go to 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
@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")
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!
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
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!
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!
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
@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!
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 , 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
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
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
@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")
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
90 | |
84 | |
71 | |
49 |
User | Count |
---|---|
141 | |
121 | |
112 | |
60 | |
58 |