Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

DAX to filter tables by regular expression

Hello,

 

I have following DAX code where I simply filter table gl by another table 'Sheet1' and sum values from gl:

Actuals = CALCULATE(SUM('public gl'[movement]),FILTER(ALL('public gl'[gl_code],'public gl'[business_unit]), 'public gl'[gl_code]='Sheet1'[gl_code] && 'public gl'[business_unit]='Sheet1'[business_unit]))
 
So Sheet1[gl_code] contains values like 1*, 2* and 101,102,201 etc. What I need is Filter to consider symbol * as regular expression and filter all gl_codes starts with 1 and 2 and also full gl_code`s like 101,102,201.
 
* symbol could be subsituted with other symbol. 
 
 
Regards,
Turkel.
 

4 Replies

  • VahidDM's avatar
    VahidDM
    Icon for Super User rankSuper User
    5 years ago

    Hi Anonymous 

     

    Try to use Left dax code in a filter, something like this:

    filter(Sheet1,left(Sheet1[gl_code],1)=1&&left(Sheet1[gl_code],1)=2)

     

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

    Appreciate your Kudos🙏!!

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Actually left DAX will not help here becouse gl_code could be 1*, 15* or full account 155 so in case of * it need to get all subaccounts.

       

      • VahidDM's avatar
        VahidDM
        Icon for Super User rankSuper User
        5 years ago

        Hi Anonymous 

         

        Can you share a sample of your data here? [In a Table Format]

         

        Appreciate your Kudos!!

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    If you really want to have something like a regex matcher in DAX... well, you can forget it. There's no such thing in DAX. DAX has a very simple string pattern matching mechanism that is not even on par with the SQL LIKE operator, so any even unsophisticated pattern matching is not possible.