Forum Discussion
Concatenate distinct values from column in Measure
Hi,
I've a table (UserPermission) with 3 columns (USERID, REGION, DIVISION). i need to filter out the data for a particular userid connected to the server and then create a measure for Region and division. Below is how the data looks like
| UserID | Region | Division |
| corp\abcded | Americas | NULL |
| corp\abcded | Americas | Globals |
| corp\abcded | Americas | NULL |
| corp\abcded | Americas | Americas |
I've tried using the below query, but the issue here is it doesn't return me the disticnt values.
RegionMeasure:=
CONCATENATEX(FILTER(RELATEDTABLE(UserPermission),UserPermission[UserId]=USERNAME()),UserPermission[Region],",")
output expected - Americas
DivisionMeasure:=
CONCATENATEX(FILTER(RELATEDTABLE(UserPermission),UserPermission[UserId]=USERNAME()),UserPermission[Division],",")
output expected - Globals,Americas (order doestn't matter)
2 Replies
- amitchandak
Super User
Anonymous , why you need related table, is not clear to me , Try like
CONCATENATEX(FILTER(UserPermission,UserPermission[UserId]=USERNAME()),UserPermission[Division],",")
- AnonymousNot applicable
Hi Amit,
It doesnt give me the distinct list, it concatenates all the values and the output will be like - "Americas, Americas, Americas, Americas".