Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Nurzh_17
Frequent Visitor

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

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

Nurzh_17_0-1681814719010.png

 

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

Nurzh_17_1-1681815035378.png

 

Appreciate for any help! 

 

1 ACCEPTED SOLUTION
Wilson_
Memorable Member
Memorable Member

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:

Wilson__0-1681827282630.png


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

View solution in original post

3 REPLIES 3
Wilson_
Memorable Member
Memorable Member

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:

Wilson__0-1681827282630.png


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

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. 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors