Change Filter with and if statement

[avachris]

Hi I would like to create a measure where the filter context changes based upon what is the selected value. 

I basically want to apply a filter for certain rows in another table and remove the filter for rows where the selectedvalue is not "x"

for example:

example measure=

var filtervalues = if selectedvalue="x" then treatas({"filter1","filter2"},dimtable[dim]) else all(dimtable))

return calculate ([measure],filtervalues)

I was having an issue with the if statement returning a table and I'm not sure how to get around this. 

[FreemanZ]

hi @avachris 

unable to follow your reasoning. dataset with expected result would be more telling.

[avachris]

Hi basically I am trying to set a dynamic table variable so that depending on a condition a different filter table is applied within the calculation context. 

I was trying to do this with a switch or an if but i think creating a variable via an iterator makes sense 

[v-jingzhang · Accepted solution]

Hi @avachris 

Both IF and SWITCH are intended to return scalar results and don't support table results.

As a workaround, you can return different scalar expressions which contain different filters according to the conditions. This is supported. For example, 

Example measure =
IF (
    SELECTEDVALUE ( 'table'[column] ) = "x",
    CALCULATE ( [measure], TREATAS ( { "filter1", "filter2" }, dimtable[dim] ) ),
    CALCULATE ( [measure], ALL ( dimtable ) )
)

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.