Unique Records in straight table

[george55]

Hi all,

Have this straight table in an app:

ID	Employee	Month	Sum	Calc2
2311	Vanessa Alice Jackson	Okt	26.3	-15.7
2311	Vanessa Jackson	Okt	26.3	-15.7
2311	Vanessa Alice Jackson	Nov	90.4	48.4
2311	Vanessa Jackson	Nov	90.4	48.4

Would like to have this output:

ID	Mitarbeiter	Month	Sum	Calc2
2311	Vanessa Alice Jackson	Okt	26.3	-15.7
2311	Vanessa Alice Jackson	Nov	90.4	48.4

Issue on this situation: Same employee with two different names. Decisive is the unique Employee-ID. So in other words, need one Name entry, doesn't matter if  "Vanessa Alice Jackson" or "Vanessa Alice". 

If possible would like to "clean" this only in one dimension or one measure, so I do not have to aggregate etc. every dimension and measure individualy (e.g. "Sum" or "Calc").

Please no solutions in the load script, because it is not a datamodell issue but rather a business typo issue.

Thanks.

[Kushal_Chawda · Accepted solution]

@george55  try below

Calculated dimension of Employee :

=aggr(maxstring(Employee),Month,ID)

Then, put distinct keyword in all measure  calculation to get the correct value.

[george55]

Thanks Kush, that worked.

Also thought to put distinct to all measure calculations, but was not necessary. Do not understand that.

[Kushal_Chawda]

@george55  It depends on what measure you have in table. If you have two records then sum function double the amount while aggregate so sum(distinct  will give unique value.. hope it is clear