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

[RMDNA]

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?

[Dangar332 · Accepted solution]

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.

[RMDNA]

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.

[Dangar332]

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.