Forum Discussion
Need to Remove Duplicated Text String in Calculated Columns
Assuming you have a table named "Projects" with a column named "Project," you can create a calculated column to store the unique values. Here's an example DAX formula:
UniqueProjects = CONCATENATEX(VALUES(Projects[Project]), Projects[Project], ", ")
This formula uses the VALUES function to get a unique list of project names and then uses CONCATENATEX to concatenate these values into a single comma-separated string.
Make sure to replace "Projects" and "Project" with your actual table and column names.
If you encounter the error "A table of multiple values was supplied where a single value was expected," it may be due to the context in which you are using the formula. Ensure that you are using it in the appropriate context, such as in a calculated column or a measure.
If you still face issues, please provide more details about your data model and the specific context in which you are using the formula so that I can provide more targeted assistance.
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.
So the problem is I need to use filters to determine if it was done by a specific user during a specific time. So where would I put the filters into the formula?
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.
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
)