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

[Nurzh_17]

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! 

[Wilson_ · Accepted solution]

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?)

[Nurzh_17]

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. 

[Wilson_]

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