DAX support - extracting a string commencing with '_'

[Anonymous]

Hi all,

I'm working on an dashboard to help me complete my timesheets based on categories I assign my calender items (meetings) + the time I spend on this calender item. I assign on average 3-4 tags/categories to a calender item. And I've created a naming convention to indicate the different contexts of a tag. (context 1 start with _; Context 2 start with .; Context 3 start with @; Context 4 start with a number)

The list of categories in powerbi - data view is seperated using ; . And the different contexts are not sorted. (they depend on the sequence when selecting the categories in outlook).

I'm looking for a DAX statement (I'm unable to install R script) that will extract the _Context 1 value.

And the closed I came in DAX is

OBJ =

TRIM (

MID (

[Categories];

IFERROR ( SEARCH ( "_"; [Categories];; 1 ); 1 );

IFERROR ( SEARCH ( ";"; [Categories];; 0 ); 0 )

)

)

Which finds me the context 1 value, but does not limit the extract to context 1.

Could someone write me the right DAX statement or point me in the right direction ?

Please find below some sample data and the expected value of the DAX statement

Input                                                                                                    Expected DAX result
_AAAAA;1.000m - Next action;@COMPUTER;.Prj - 11111                  _AAAAA
_BBBB;@AGENDA                                                                                _BBBB
_AAAAA;.Prj - 11111                                                                            _AAAAA
.Prj - 11111;_AAAAA;1.000m - Next action;@COMPUTER                  _AAAAA
_AAAAA;1.000m - Next action;@AGENDA;.Prj - 11111;@CALLS        _AAAAA
_CCCCCC;@COMPUTER                                                                      _CCCCCC
_EEEEE;@AGENDA                                                                               _EEEEE
_DDDDD;.Prj - 222;.Prj - 33333                                                            _DDDDD
_DDDDD;.Prj - 222                                                                               _DDDDD
_BBBB;@AGENDA                                                                                _BBBB
_BBBB;@AGENDA                                                                                _BBBB
@COMPUTER;_BBBB                                                                           _BBBB
.Prj - 33333;_DDDDD                                                                           _DDDDD
_AAAAA;.Prj - 11111                                                                            _AAAAA
_EEEEE;.Prj - 222                                                                                   _EEEEE
_AAAAA;.Prj - 11111                                                                            _AAAAA
_EEEEE;.Prj - 222                                                                                   _EEEEE
@COMPUTER;_EEEEE                                                                           _EEEEE
@COMPUTER;_DDDDD                                                                        _DDDDD
_AAAAA;.Prj - 11111                                                                             _AAAAA
.33333;_BBBB                                                                                        _BBBB
_FFFFF                                                                                                   _FFFFF

Many thanks for your support.

[ChrisMendoza · Accepted solution]

@Anonymous-

Hopefully someone comes up with something prettier 🙂

Final Solution = 
VAR __NumChars =
    SEARCH ( ";", TableName[Input], 1, 0 ) - SEARCH ( "_", TableName[Input], 1, 0 )
VAR __Positive =
    IF ( SIGN ( __NumChars ) > 0, TRUE (), FALSE () )
VAR __NotPositive =
    IF (
        NOT ( __Positive ),
        IFERROR (
            SEARCH ( "_", TableName[Input], SEARCH ( ";", TableName[Input], 1, 0 ), -1 ),
            -1
        )
    )
RETURN
    IF (
        __Positive,
        LEFT ( TableName[Input], __NumChars ),
        IF (
            IFERROR ( SEARCH ( ";", TableName[Input], __NotPositive, 0 ), -1 ) > 0,
            MID (
                TableName[Input],
                SEARCH ( "_", TableName[Input], 1, 0 ),
                IFERROR ( SEARCH ( ";", TableName[Input], __NotPositive, 0 ), -1 )
                    - SEARCH ( "_", TableName[Input], 1, 0 )
            ),
            IF (
                IFERROR ( SEARCH ( ";", TableName[Input], __NotPositive, 0 ), -1 ) = 0,
                RIGHT (
                    TableName[Input],
                    LEN ( TableName[Input] )
                        - IFERROR ( SEARCH ( "_", TableName[Input], 1, 0 ), -1 ) + 1
                ),
                TableName[Input]
            )
        )
    )

[Anonymous]

This did the trick. I did had to change the ',' by ';' but this is probably related to my local language setting.

Many thanks to this active forum & your support 🙂

[Anonymous]

Hi @Anonymous 

you can get something very close by using "Split Column" when you import your data in the query editor.

split by _ then by ; then delete the columns you don't need.

[Anonymous]

Thx for the insight.