Forum Discussion

spider_Monkey's avatar
spider_Monkey
Helper I
2 years ago

Need to Remove Duplicated Text String in Calculated Columns

Hello,  I need to remove duplicate project names and I used the dax below (ignore red needed to remove identifying data). If you remove the Values It currently shows up as: Project: A, A, A, B, B...
"DAX help"
Duplicate Values
123abc's avatar
123abc
Community Champion
2 years ago

If you need to filter the project names based on specific criteria such as the user who performed the task and the time frame during which the task was performed, you can incorporate these filters into the DAX expression. Assuming you have a column named UserID in your Timesheet table and you want to filter based on a specific user ID and a time range, you can modify the DAX expression as follows:

 

UniqueProjects =
VAR CurrentUserID = Timesheet[UserID]
VAR StartDate = Timesheet[StartDate]
VAR EndDate = Timesheet[EndDate]
RETURN
CONCATENATEX(
FILTER(
VALUES(ProjectsTable[ProjectName]),
ProjectsTable[UserID] = CurrentUserID &&
ProjectsTable[Date] >= StartDate &&
ProjectsTable[Date] <= EndDate
),
ProjectsTable[ProjectName],
", ",
ProjectsTable[ProjectName],
ASC
)

 

In this modified expression:

  • CurrentUserID is set to the user ID for the current row in the Timesheet table.
  • StartDate and EndDate are set to the start and end dates for the current row in the Timesheet table.
  • The FILTER function filters the rows from the ProjectsTable based on the following conditions:
    • The user ID matches the CurrentUserID.
    • The date falls within the specified range between StartDate and EndDate.
  • CONCATENATEX concatenates the filtered project names into a comma-separated list.

Make sure to replace UserID, StartDate, EndDate, and ProjectsTable with the actual column names and table name from your data model.

This expression should create a calculated column in the Timesheet table that displays unique project names based on the specified user ID and time frame filters. Adjust the column names and conditions according to your actual data model and requirements.

 
 
 
 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

 

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

spider_Monkey's avatar
spider_Monkey
Helper I
2 years ago

123abc  So I can't get it to work, and I get an error when I leave VALUES(ProjectsTable[ProjectName]) in there. If i just use VALUES(ProjectsTable) I get a list but unforunetly it doesn't give me a single list of projects it's still giving me AAAA,BB,CCC. 

 

I want it just to return A, B,C regardless of how many times they work on the project. 

 

UniqueProjects =
VAR CurrentUserID = Timesheet[UserID]
VAR StartDate = Timesheet[StartDate]
VAR EndDate = Timesheet[EndDate]
RETURN
CONCATENATEX(
FILTER(
VALUES(ProjectsTable[ProjectName]),
ProjectsTable[UserID] = CurrentUserID &&
ProjectsTable[Date] >= StartDate &&
ProjectsTable[Date] <= EndDate
),
ProjectsTable[ProjectName],
", ",
ProjectsTable[ProjectName],
ASC
)