topic DAX to filter tables by regular expression in DAX Commands and Tips

DAX to filter tables by regular expression

Anonymous — Mon, 13 Sep 2021 12:11:49 GMT

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.

Re: DAX to filter tables by regular expression

VahidDM — Mon, 13 Sep 2021 13:22:50 GMT

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🙏!!

Re: DAX to filter tables by regular expression

Anonymous — Mon, 13 Sep 2021 13:56:13 GMT

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.

Re: DAX to filter tables by regular expression

VahidDM — Tue, 14 Sep 2021 00:47:09 GMT

Hi Anonymous

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

Appreciate your Kudos!!

Re: DAX to filter tables by regular expression

Anonymous — Wed, 15 Sep 2021 15:30:00 GMT

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.