Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Tinus1905
Resolver I
Resolver I

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])
 
Filter slicer.png
7 REPLIES 7
AilleryO
Memorable Member
Memorable Member

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

danextian
Super User
Super User

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.

 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

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. 

ChiragGarg2512
Super User
Super User

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

Thanks, I'll go through it

AilleryO
Memorable Member
Memorable Member

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

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.