Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

DAX support - extracting a string commencing with '_'

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.

Need Help
  • ChrisMendoza's avatar
    ChrisMendoza
    7 years ago

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

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      Thx for the insight.

  • ChrisMendoza's avatar
    ChrisMendoza
    Resident Rockstar
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

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