Forum Discussion

Tinus1905's avatar
Tinus1905
Resolver I
2 years ago

Filter slicer with IF function

Hello, 

 

I want to use my slicer and when I select the value "Cars" then the output in my card has to be "Cars measure", when I select the value "Music" then the output in my card has to be "Music measure". 

I'm getting a error using the following measure:  

Filter = IF(FILTER('Sample Table', 'Sample Table'[Value3] = "Cars"), [Cars measure], [Music measure])
 
Need Help

7 Replies

  • danextian's avatar
    danextian
    Super User
    2 years ago

    Hi Tinus1905 ,

    You don't use filter in this case.  Filter returns a table and cannot be used in a measure without being aggregated.

     

    The old school to achieve your end result as by creating a disconnected table containing the measure you want which can be created using the Enter Data feature or DAX then use a IF or SWITCH to return the measure you want. Example

    Switching Measure =
SWITCH (
    SELECTEDVALUE ( 'disconnected table'[measure name] ),
    "Cars", [Cars Measure],
    "Music", [Music Measure],
    "Something", [Something Measure]
)

    A faster and more efficient way to do is with the use of Field Parameters.

     

    • Tinus1905's avatar
      Tinus1905
      Resolver I
      2 years ago

      Ah this works. But how do I use "NOT" in a value. For example: in the disconnected table [measure name] the list is longer with more names. 

          "Cars", [Cars Measure],
    "Music", [Music Measure],
    "<>Cars", [Something Measure]
    "<>Music", [Something Measure]

      The function <> isnt working. 

  • AilleryO's avatar
    AilleryO
    Memorable Member
    2 years ago

    Hi,

    Be carefull with FILTER function which is an equivalent to CALCULATETABLE, so it means it returns a table which is not a valid argument for IF function.

    You just need your test :

    Filter = IF( 'Sample Table'[Value3] = "Cars", [Cars measure], [Music measure])

    Hope it helps

    • Tinus1905's avatar
      Tinus1905
      Resolver I
      2 years ago

      With thisd I get a error: 

      A single value for column 'Value3' in table 'Sample Table' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

  • ChiragGarg2512's avatar
    ChiragGarg2512
    Solution Sage
    2 years ago

    Tinus1905 Use field parameters.

     

    Videos and blogs for reference:

    Power BI Field Parameters — A Quick way for Dynamic Visuals: https://amitchandak.medium.com/power-bi-field-parameters-a-quick-way-for-dynamic-visuals-fc4095ae9afd
    Power BI Field Parameters- Measure Slicer and Axis/Dimension slicer: https://youtu.be/lqF3Wa1FllE
    Switch TOPN with Field Parameters: https://amitchandak.medium.com/switch-topn-with-field-parameters-299a0ae3725f
    Field Parameters- Conditional Formatting
    : https://amitchandak.medium.com/field-parameters-conditional-formatting-517aacc23fdf

  • AilleryO's avatar
    AilleryO
    Memorable Member
    2 years ago

    I do not know exactly what you need  (single or multiple value ?), but you can try :

    Filter = IF( SELECTEDVALUE('Sample Table'[Value3]) = "Cars", [Cars measure], [Music measure])

     Works with single values, but shloud be your case, isn't it ?

    Let us know