Forum Discussion

avachris's avatar
avachris
Frequent Visitor
3 years ago
Solved

Change Filter with and if statement

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 f...
  • v-jingzhang's avatar
    v-jingzhang
    3 years ago

    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.

FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper User
3 years ago

hi avachris 

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

avachris's avatar
avachris
Frequent Visitor
3 years ago

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's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    3 years ago

    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.