Matching the last occurrence of string pattern with DAX

[rssilvaba]

I am trying to get the text between following pattern with DAX: 
__ tjer 3(4x22) D __
______ T__r RTY 15x48.8kg TY A_
PS: the underscore is just to hide the data, assume it is any character.
I would need the numbers before the x. in the examples above that would be 4 and 15.

I can find an occurence with the DAX below: 

SEARCH("?x*",SELECTEDVALUE(AnalisiOTR[MaterialDescr]),1,0)

The problem is that this will basically match any thing that has a x between so if I have an x in the begging I get the wrong position.

Any ideas how I can achieve that with DAX? I can't modify the data with PowerQuery that is why I am asking for a DAX solution.

[Icey · Accepted solution]

Hi @rssilvaba ,

Last occurrence of "x" Column = 
FIND (
    "@",
    SUBSTITUTE (
        [Column1],
        "x",
        "@",
        LEN ( [Column1] ) - LEN ( SUBSTITUTE ( [Column1], "x", "" ) )
    )
)

Last occurrence of "x" Measure = 
FIND (
    "@",
    SUBSTITUTE (
        MAX ( 'Table'[Column1] ),
        "x",
        "@",
        LEN ( MAX ( 'Table'[Column1] ) )
            - LEN ( SUBSTITUTE ( MAX ( 'Table'[Column1] ), "x", "" ) )
    )
)

Then, I can get the string before the last "x". 

String before the last "x" = LEFT([Column1],[Last occurrence of "x" Column]-1)

But currently I don't know how to get the last several numbers. Maybe you can try the method @swise001 provided.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rssilvaba]

Hi @Icey , You awnser works great, I came with a well using a sloppy recursion, but this is much nicer.

Thank you very much for the detailed anwser.

[swise001]

@rssilvaba 

This can be done using Calculated Columns

Location_of_x =

SEARCH ( "x", ( AnalisiOTR[MaterialDescr] ), 1, 0 )

Has2NumbersPreceding =

IFERROR (

VALUE ( MID ( AnalisiOTR[MaterialDescr], AnalisiOTR[Location_of_x] - 2, 1 ) ),

    0

)

Return Number(s) before x =

VALUE (

IF (

AnalisiOTR[Has2NumbersPreceding] >= 1,

MID ( AnalisiOTR[MaterialDescr], AnalisiOTR[Location_of_x] - 2, 2 ),

MID ( AnalisiOTR[MaterialDescr], AnalisiOTR[Location_of_x] - 1, 1 )

)

)

If you require using DAX as a measure - it may require some tweaking still though.  

[rssilvaba]

Hi @swise001 ,

Your solution seems to follow the same principle as mine, so f there are any x's on the way it will just grab it. I tried it here and the result of the position is the one number up as the measure I used. That is why I am trying to find the last occurence. Also the last column gives me an error.

[swise001]

@rssilvaba 

The last column formula throws an error when the x is preceded by a letter/symbol.   To fix it - just add some error handling: 

Return Number(s) before x =

IFERROR(

VALUE (

IF (

AnalisiOTR[Has2NumbersPreceding] >= 1,

MID ( AnalisiOTR[MaterialDescr], AnalisiOTR[Location_of_x] - 2, 2 ),

MID ( AnalisiOTR[MaterialDescr], AnalisiOTR[Location_of_x] - 1, 1 )

)

)

,0)

As for the issue with 'x' at the beginning - you can 'cue' up the search to start past these initial x's by starting at say position 5 instead of 1.  

Location_of_x =

SEARCH ( "x", ( AnalisiOTR[MaterialDescr] ), 5, 0 )

Here what this looks like: 

If the issue with starting "x's" is more complex - you'll just have to continue to tweak how you direct the search formula to start.  

[amitchandak]

@ImkeF , Can you help on this

[ImkeF]

Hi @rssilvaba ,

how does one know which x to take?

[rssilvaba]

Hi @ImkeF , the pattern will end with auppercase letter then a number, space and two other uppercase letters.
So if I can get the last occurrence bettween that pattern it should work fine. Here are some examples how the string ends.
N4 AW
T4 UT