- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Report Filter Switches Which Measure is Displayed
Hello!
I have a use case where I have two measures that need to be displayed in a visual. However, each measure only applies to certain dimension values, so I need the visual to switch measures depending on a report filter. I currently have this DAX formula. However, if a filter value doesn't exist in the prior year, then it uses the wrong measure for the specific line item and still uses the correct measure for everything else.
This is my measure that switches.
Measure1 or Measure2 =
SWITCH(
TRUE(),
[Filter User Selection] = "A", Measure1],
[Filter User Selection] = "B", [Measure1],
[Filter User Selection] = "C", [Measure1],
[Filter User Selection] = "D", [Measure1],
[Filter User Selection] = "E", [Measure2],
ISBLANK([Filter User Selection]), [Measure1]
)
This is my report filter.
Filter User Selection = SELECTEDVALUE('Table'[Field])
Year over year, the highlighted values are actually using Measure2 and everything else is using Measure1. When I looked at the data, it was because that group didn't have any records that matched [Filter User Selection] = "A", for example, that was selected in the report filter, but it did have [Filter User Selection] = "E". Is there a way to prevent that?
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @aliasch ,
Thanks Sahir_Maharaj for the quick reply. I have some other thoughts to add:
(1) We need two tables. We use the table as a slicer table and table 2 as a fact table with data such as year. Note that there is no model relationship between the two tables.
(2) We can create a measure.
Measure = IF(SELECTEDVALUE('Table'[Field])="E",[Measure2],[Measure1])
(3) Then the result is as follows.
Select E only:
Select other only:
All or None or Multiple Choice:
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @aliasch ,
Thanks Sahir_Maharaj for the quick reply. I have some other thoughts to add:
(1) We need two tables. We use the table as a slicer table and table 2 as a fact table with data such as year. Note that there is no model relationship between the two tables.
(2) We can create a measure.
Measure = IF(SELECTEDVALUE('Table'[Field])="E",[Measure2],[Measure1])
(3) Then the result is as follows.
Select E only:
Select other only:
All or None or Multiple Choice:
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @aliasch
Try this:
Cretae a measure:
Filter User Selection =
SELECTEDVALUE( ALLSELECTED('Table'[Field]) )
and then this measure:
Measure1 or Measure2 =
VAR FilterSelection = [Filter User Selection]
RETURN
SWITCH(
TRUE(),
ISBLANK(FilterSelection), [Measure1],
FilterSelection = "A", [Measure1],
FilterSelection = "B", [Measure1],
FilterSelection = "C", [Measure1],
FilterSelection = "D", [Measure1],
FilterSelection = "E", [Measure2],
[Measure1] // Default case if none of the conditions match
)
Since "A" to "D" all use Measure1, you can simplify the conditions:
Measure1 or Measure2 =
VAR FilterSelection = [Filter User Selection]
RETURN
IF(
ISBLANK(FilterSelection),
[Measure1],
IF(
FilterSelection = "E",
[Measure2],
[Measure1] // For all other cases
)
)
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello @aliasch,
Can you please try this approach:
Measure1_or_Measure2 =
SWITCH(
TRUE(),
[Filter User Selection] = "A" && NOT(ISBLANK(Measure1)), Measure1,
[Filter User Selection] = "B" && NOT(ISBLANK(Measure1)), Measure1,
[Filter User Selection] = "C" && NOT(ISBLANK(Measure1)), Measure1,
[Filter User Selection] = "D" && NOT(ISBLANK(Measure1)), Measure1,
[Filter User Selection] = "E" && NOT(ISBLANK(Measure2)), Measure2,
ISBLANK([Filter User Selection]) || ISBLANK(Measure1), Measure1,
Measure2
)
Hope this helps 🙂
Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂
Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution? (Yes, its FREE!)
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Unfortunately, that didn't work 😞 I had the same issue where one filter value that had Measure 1 this year but not last year show up with Measure 2. Essentially I just need [Filter User Selection] = "E" to show Measure 2 and the rest, including all filter combinations, to show Measure 1. Even with all values selected, I want to show Measure 1. It's just when [Filter User Selection] = "E" only, then I need Measure 2. Maybe there's a different route entirely?

Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - February 2025
Check out the February 2025 Power BI update to learn about new features.

Subject | Author | Posted | |
---|---|---|---|
01-02-2024 09:43 AM | |||
08-13-2024 01:40 PM | |||
08-02-2024 08:59 AM | |||
07-08-2023 10:37 AM | |||
03-28-2024 03:38 AM |
User | Count |
---|---|
24 | |
12 | |
11 | |
10 | |
9 |
User | Count |
---|---|
18 | |
14 | |
13 | |
12 | |
10 |