topic Re: How to find values that contains the same string and mark them that this number is not Single in DAX Commands and Tips

How to find values that contains the same string and mark them that this number is not Single

Anonymous — Tue, 18 Apr 2023 10:51:22 GMT

Hi Everyone!

I have a table Document, and I want to add calculated column that shows me if each number present in dataset with copy or master category.

All documents are divided into 4 categories: 1. Master Document 2. Project Document 3. Project Working Copy 4. Working Copy

1,2 - is Master, 3,4 - is Copy.

Copy number always comes after the master number through a dash. And the length of document numbers is not fixed, it can be different

For example, in this case 1-300_1 -B-2002 is the master, and everything after it through a dash is already a copy

Puprose of this task is to delete from the dataset numbers that present single without any copies.

For example, there are numbers that do not have a copy, they go alone, they need to be removed

Appreciate for any help!

Re: How to find values that contains the same string and mark them that this number is not Single

Wilson_ — Tue, 18 Apr 2023 14:15:35 GMT

Hello Nurzh,

To clarify, are all the non-highlighted rows in your first image copies of the highlighted row? If so, the below should work in a calculated column:

Number of Copies = VAR MasterDocument = Table1[DocumentSourceCategory] IN { "Master Document", "Project Document" } VAR DocNum = Table1[DocumentNumber] VAR DocNumLength = LEN ( Table1[DocumentNumber] ) VAR DocNumFilteredTable = -- filters for all documents that have the same "prefix" as that row's document number FILTER ( Table1, LEFT ( Table1[DocumentNumber], DocNumLength ) = DocNum ) VAR Result = IF ( MasterCopy, -- checks that current row is a master document row COUNTROWS ( DocNumFilteredTable ) -- counts records from above filtered table ) RETURN Result

I built a tiny sample dataset (and added the above as a calculated column). The results are as below:

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

Re: How to find values that contains the same string and mark them that this number is not Single

Anonymous — Wed, 19 Apr 2023 11:12:34 GMT

Hello Wilson,

Thanks a lot for your solution of this case. But I did not check this formula, because before you provide the formula, I decided to go by another way and ot works for me. I created a calculated column that will show the DocumentNumber itself, if it is a master, if the DocumentNumber refers to a copy, then show all numbers without the last dash and characters after it:

Document cut = if(Document[DocumentSourceCategory] in {"Master Document","Project Document"}, Document[DocumentNumber], LEFT(Document[DocumentNumber],FIND("#",SUBSTITUTE(Document[DocumentNumber],"-","#", LEN(Document[DocumentNumber])-LEN(SUBSTITUTE(Document[DocumentNumber],"-",""))))-1) )

After this step marked duplicated values in this column:

Duplication check = var _curentRowID = Document[Document cut] return COUNTROWS(FILTER(ALL(Document),_curentRowID = Document[Document cut]))

And finally get to another table all values that does not equal 1 to show in dataset documents only with copy or master category.

Re: How to find values that contains the same string and mark them that this number is not Single

Wilson_ — Wed, 19 Apr 2023 14:21:09 GMT

Sounds good, Nurzh. Looks like we converged on very similar solutions. Glad you were able to solve this yourself. 😄