Report Filter Switches Which Measure is Displayed

[aliasch]

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?

[v-tangjie-msft · Accepted solution]

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. 

[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!! 

LinkedIn|Twitter|Blog |YouTube 

[Sahir_Maharaj]

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 🙂

[aliasch]

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?