Forum Discussion
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?
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.
4 Replies
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.
- Sahir_Maharaj
Super User
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 🙂
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?
- VahidDM
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!!
