topic Re: Find the text from string and remove remain part in DAX Commands and Tips

Find the text from string and remove remain part

Nurzh — Mon, 29 May 2023 21:06:05 GMT

Hi all,

I would like to create a new column in a table that will show me number of Master Document for Project Copies.

Example:

Table "Document"

Document Number	Document Category
61-2300-P-5090	Master Document
61-2300-P-5090-07812	Project Working Copy
61-2300-P-5090-05908P	Project Working Copy
61-2300-P-5090-05908T1	Project Working Copy
61-2300-P-5090-05908T	Project Working Copy
61-2300-P-5090-07812-21	Project Working Copy

Desired New Column that will identify us the Number of Master Document of this copies:

Document Number	Document Category	New column (Master Documents)
61-2300-P-5090	Master Document	61-2300-P-5090
61-2300-P-5090-07812	Project Working Copy	61-2300-P-5090
61-2300-P-5090-05908P	Project Working Copy	61-2300-P-5090
61-2300-P-5090-05908T1	Project Working Copy	61-2300-P-5090
61-2300-P-5090-05908T	Project Working Copy	61-2300-P-5090
61-2300-P-5090-07812-21	Project Working Copy	61-2300-P-5090

One more thing is the lenght of Master Documents can be different and we need to check it by Category.

I tried this code, but it does not work correctly:

document master =

VAR CurrentCategory = Document[DocumentSourceCategory]

VAR CurrentNumber = Document[DocumentNumber]

VAR IsMasterDocument = CurrentCategory = "Master Document"

VAR MasterNumber =

IF(

IsMasterDocument,

CurrentNumber,

LEFT(CurrentNumber, IFERROR(FIND("-", CurrentNumber) - 1, LEN(CurrentNumber))

)

RETURN

MasterNumber

Thank you for any help!

Re: Find the text from string and remove remain part

barritown — Tue, 30 May 2023 13:52:46 GMT

Hi Nurzh,

Please try this calculated column versus your data:

And in plain text for convenience:

Document Master = VAR CurrentNumber = [Document Number] RETURN MINX ( FILTER ( ADDCOLUMNS ( FILTER ( ALL ( Document ), Document[Document Category] = "Master Document" ), "Match", VAR CurrentMaster = [Document Number] RETURN CONTAINSSTRING ( CurrentNumber, CurrentMaster ) ), [Match] = TRUE () ), [Document Number] )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

Re: Find the text from string and remove remain part

Nurzh — Wed, 31 May 2023 04:29:32 GMT

Hi barritown ,

Thank you for your solution,

it really works, but I have 2.8 million rows in my table, and now almost 6-7 hours I am waiting for the result, it just stuck on "Working on it"...

Is there any other ways to realise it, maybe in Power query?

Re: Find the text from string and remove remain part

barritown — Wed, 31 May 2023 07:16:13 GMT

Hi Nurzh,

I knew my solution is greedy in terms of resources, but 6-7 hours - wow.

I will think how to opimize my [DAX] solution - not sure that I will be able to turn hours into minutes, but I'll try. Before that, could you please clarify how many "master" entries you have among those 2.8 million rows?

As for the Power Query solution, maybe it is worth duplicating this post in the "Power Query" section of this forum - I guess Power Query gurus live there. 🙂

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian