Switch with Multiple Hasonevalue() gives wrong results

[MikhailGG]

I have the following formula

Overview_Switch = switch( true(),

                                    HASONEVALUE('Data'[Region]), median('Sales_by_Region'[Total]),

                                    HASONEVALUE('Data'[Country]),median('Sales_by_Country'[Total]),

                                    HASONEVALUE('Datay'[Functional Area]), median('Sales_by_Func_Area'[Total]),

                                    HASONEVALUE('Data'[Manager]), median('Sales_by_Manager'[Total]),

                                    median('Data'[Official_Total])

)

The goal is essentially to feed card visual with the proper value based on choosing different values in the visuals of Region, Country, Functional Area or Manager.:

However, it looks like I'm getting incorrect results if more than 1 statements in switch falls under a single value. In that case whichever statement comes first is the one that populates the card.

How can I change the formula to make the results accurate?

[MikhailGG · Accepted solution]

iffiltered() insted of hasonevalue() solved the issue for me

[Anonymous]

Hi @MikhailGG,

Glad your issue has been resolved!
I suggest you to accept your own post as the solution — it will help other community members facing similar problems to find the answer faster.

Regards,

Vinay Pabbu

[Tutu_in_YYC]

The switch statement evaluate in sequence, so if the first statement is true, it will stop there.

If you need to evalute combinations of multiple statements, you might have to do something like this:

VAR _statement1 =  HASONEVALUE('Data'[Region])
VAR _statement2 =  HASONEVALUE('Data'[Country])
VAR _statement3 =  HASONEVALUE('Data'[Functional Area])

SWITCH(
   TRUE(),
   Statement1 && Statement2 && Statement3, Result1,
   Statement1 && Statement2, Result2,
   Statement1, Result3,
   Result4
)

[MikhailGG]

That is the problem. How can I ignore the sequence if I slice by Functional Area, but the chosen functional area has only single region and since region comes before functional area in the switch statement, I get results for region instead of functional area? Is there a different approach besides switch?

Each of those tables: sales_by_region, sales_by_country and etc have slightly different values for Total. They just have different weights depending on the table.

[Deku]

Can you share your data model, I think you're problem is modelling maxing the DAX much more complicated than it needs to be. 

Realistically you should 

• Geography dimension( region and country)
• Functional area dimension
• Manager dimension
• Sales fact

Then the measure would just be median( sales[offical_total] ) and you can slice and dice however you want.

Your current issue what happens if some selects filter from multiple slicers, they will likely get misleading results

[Tutu_in_YYC]

this ☝️