topic Re: Store filter criteria as a string in a variable so I don't have to keep typing it in DAX Commands and Tips

Store filter criteria as a string in a variable so I don't have to keep typing it

RMDNA — Tue, 06 Aug 2024 19:16:10 GMT

Hi all, hopefully quick question here. I'm writing some simple statements in DAX Studio like:

CALCULATE(COUNTROWS(Table),Category1 = "Value',Category2 = "Value2") = 100

This works fine, but I don't want to keep typing my filters (Cat1=, Cat2=,etc.) over and over again. Can I somehow capture that into a string and just use that in my post-calculate filter statements? I'd like to set up a bunch of filters at the start of the statement and just reference them throughout the rest of the script. E.g.:

VAR filter_criteria = (Category1 = "Value',Category2 = "Value2") EVALUTE CALCULATE(COUNTROWS(Table),filter_criteria) = 100

Or am I missing an easier solution?

Re: Store filter criteria as a string in a variable so I don't have to keep typing it

Dangar332 — Tue, 06 Aug 2024 19:35:53 GMT

Hi, RMDNA

Try below measure

EVALUTE var filter_criteria= filter(table,Category1 = "Value'&& Category2 = "Value2" && category3="Value3"&&....) return CALCULATE(COUNTROWS(Table),filter_criteria)

Best regards,
Dangar

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

Re: Store filter criteria as a string in a variable so I don't have to keep typing it

RMDNA — Tue, 06 Aug 2024 20:20:46 GMT

Hi Dangar332 - what if I wanted to filter on multiple tables? E.g.

(Table1[Field1] = x, Table2[Field2] = y || Table2[Field2] = z)

Since they included filtering as a native ability of Calculate, I've stopped using the actual Filter function for a long time. I believe it can only accept a single table as a reference. Do I need to nest Filters? Seems inefficient.

Re: Store filter criteria as a string in a variable so I don't have to keep typing it

Dangar332 — Tue, 06 Aug 2024 21:09:58 GMT

Hi, RMDNA

You can use Crossjoin ()

filter( crossjoin( values(Table1[Field1]), values( Table2[Field2]) ), Table1[Field1] = x && Table2[Field2] = y)

Best regards,
Dangar

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