Hi all
Hope you enjoy your weekend
I am checking if there’s a standard function or data feature which could suppress duplicates in the data set. I am also looking for a formula which i can put in the calculated dimension (data-set level), which could flag the duplicated values
On the screenshot below, i would like to flag the first occurrence of duplicated IDS (Date ASC) per Team field.
I was thinking using the Lead function, which returns the previous or next field value, but the duplicates might be positioned not necessarily next to each other
Would appreciate any advise how to resolve this
Hi all,
I have made the correct formula, which calculates the duplicate flag properly:
ifelse(lead(ID, [Team ASC, ID ASC], 1, [Team])=firstValue(ID,[Team ASC, ID ASC], [Team, ID]),‘1’,‘’)
But apparently, it cannot be used as the calculated filter generally for this data set, e.g. if i want to create the pie chart with the share of Count(ID) per team
Is there any way to have 1 calculated filter which can still work on the line level but still be applied on any visuals?
I find it a bit weird that i have to adapt every single calculated dimension per different set of fields added to different visuals
Many thanks in advance 
Hi @AndriyShepel,
Is there a particular reason why you’re not taking the distinct count of IDs per team? In your example, what is the correct count for team A? Is it 5?
If you still want to create a calculated field to identify your duplicates, you can try the rank function.
rank([{Date} ASC], [{Team}, {ID}], PRE_AGG)
Any value greater than 1 would be a duplicate.
1 Like
Hi David
Thanks for your reply
The main reason why i don’t wanna use an additional operators like distinct and others is that i would like keep the data clean at the dataset level, so the formulas do not get overcomplicated 
Many thanks for the advice of using the rank function. Hope it can work as the filter throughout all the visuals. Let me check it out
Awesome, works as magic
I would of course love to learn the specifics of using PRE_AGG and other similar operators
1 Like