Forum Discussion
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,B,C,C,D,D,D
What I want is:
Project: A,B,C,D
I thought I could get that with Values (See Below) at the end but it gives me "A table of multiple values was supplied where a single value was expected."
I thought this would get me close @https://community.fabric.microsoft.com/t5/Desktop/To-remove-duplicate-char-from-string-on-each-row-using-DAX/m-p/2896296
6 Replies
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.
thanks for the reply 123abc, the problem is I need to use the filters to make sure it's within the date range as well as the ID's need to match.
I forgot to mention I need to make this column on the timesheet table
I am looking up the project names off a different table so not quite sure how to do that with this one!
Hi spider_Monkey ,
Can you share sample data and sample output in table format? We can better understand the problem and assist you. Show it as a screenshot or excel. Please remove any sensitive data in advance.
Best Regards,
Yulia Yan
