DAX to filter tables by regular expression

[Anonymous]

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.

[Anonymous]

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.

[VahidDM]

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]

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]

Hi @Anonymous 

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

Appreciate your Kudos!!