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 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. 

  • 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.

3 Replies

  • 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.