DAX Formula help

[Nizamrcl]

Hi,

I am looking for a formula of DAX to find a specific number in invoice column

Eg: Invoice number always start with number 2 and it has 8 digits as shown in below table (highlighted)

[TABLE="class: grid, width: 250, align: center"]
<tbody>[TR]
[TD][TABLE="width: 298"]
<tbody>[TR="class: grid"]
[TD="width: 298"]KB335/21635956/A-AMOUNT-R091018-R171018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 298"]
<tbody>[TR="class: grid"]
[TD="width: 298"]21600589/A-2 (INVOICE VALUE EXCEEDS ORDER VALUE)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 298"]
<tbody>[TR="class: grid"]
[TD="width: 298"]KB335/21600589/A-AMOUNT MISMATCH[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 298"]
<tbody>[TR="class: grid"]
[TD="width: 298"]21709491/B[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 298"]
<tbody>[TR="class: grid"]
[TD="width: 298"]KB335/21718253[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 298"]
<tbody>[TR="class: grid"]
[TD="width: 298"]21683908/B (ORDER NOT AVAILABLE)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 298"]
<tbody>[TR="class: grid"]
[TD="width: 298"]8B918/21670664[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 298"]
<tbody>[TR="class: grid"]
[TD="width: 298"]8B918/21705980/B-1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 298"]
<tbody>[TR="class: grid"]
[TD="width: 298"]8B918/21721500[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 298"]
<tbody>[TR="class: grid"]
[TD="width: 298"]21722557[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 298"]
<tbody>[TR="class: grid"]
[TD="width: 298"]8B918/21722328[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 298"]
<tbody>[TR="class: grid"]
[TD="width: 298"]FAPR1/21661374/A R030319 -Amount mismatch[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 268"]
<tbody>[TR="class: grid"]
[TD="width: 298"]X1360/21621259[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aryatect]

Hi,

I had stored this mixed data in the "Table2" with column name "Mix". Below DAX formula should work for you (This is similar in Excel formula too)

=IFERROR(MID(Table2[Mix],SEARCH("/2",Table2[Mix])+1,8),LEFT(Table2[Mix],8))

Book1
	A	B
1	Mix	Invoice
2	21722557	21722557
3	21600589/A-2 (INVOICE VALUE EXCEEDS ORDER VALUE)	21600589
4	21683908/B (ORDER NOT AVAILABLE)	21683908
5	21709491/B	21709491
6	8B918/21670664	21670664
7	8B918/21705980/B-1	21705980
8	8B918/21721500	21721500
9	8B918/21722328	21722328
10	FAPR1/21661374/A R030319 -Amount mismatch	21661374
11	KB335/21600589/A-AMOUNT MISMATCH	21600589
12	KB335/21635956/A-AMOUNT-R091018-R171018	21635956
13	KB335/21718253	21718253
14	X1360/21621259	21621259
Sheet3

Cell Formulas
Range	Formula
B2	=IFERROR(MID(A2,SEARCH("/2",A2)+1,8),LEFT(A2,8))

 

[Nizamrcl]

It's done - Thanks a lot Aryatect

 

[Nizamrcl]

ooops

incase if the line has blanks (other than /2) then this is not working

eg: [TABLE="width: 455"]
<tbody>[TR]
[TD="class: xl63, width: 455"]X1594 21209478 G4322094/096/097[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 455"]
<tbody>[TR]
[TD="class: xl65, width: 455"]X1360R 21342611

Kindly help[/TD]
[/TR]
</tbody>[/TABLE]

 

[citizenbh]

If there are no additional variants, import Table2 (Column Mix) into the Power Query and in the Advanced Editor paste the following code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Mix", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Mix", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Mix.1", "Mix.2", "Mix.3"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Mix.1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Mix.1.1", "Mix.1.2", "Mix.1.3"}),
    #"Added Custom" = Table.AddColumn(#"Split Column by Delimiter1", "AllFirst2", each if Text.Length([Mix.1.1])=8 and Text.Start([Mix.1.1],1)="2" then [Mix.1.1] else if Text.Length([Mix.1.2])=8 and Text.Start([Mix.1.2],1)="2" then [Mix.1.2] else [Mix.2])
in
    #"Added Custom"

New Column "AllFirst2" load in Data Model

 

[Aryatect]

Hi Nizamrcl,

If that is the only other case and you don't want to use Power Query which is a great solution too, a quick change to the formula below will work:

=IFERROR(IFERROR(MID(Table2[Mix],SEARCH("/2",Table2[Mix])+1,8),MID(Table2[Mix],SEARCH(" 2",Table2[Mix])+1,8)),LEFT(Table2[Mix],8))

ooops

incase if the line has blanks (other than /2) then this is not working

eg: [TABLE="width: 455"]
<tbody>[TR]
[TD="class: xl63, width: 455"]X1594 21209478 G4322094/096/097[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 455"]
<tbody>[TR]
[TD="class: xl65, width: 455"]X1360R 21342611

Kindly help[/TD]
[/TR]
</tbody>[/TABLE]