Forum Discussion

MikhailGG's avatar
MikhailGG
Icon for Helper I rankHelper I
1 year ago
Solved

Switch with Multiple Hasonevalue() gives wrong results

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?

Need Help
  • MikhailGG's avatar
    MikhailGG
    1 year ago

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

6 Replies

  • Deku's avatar
    Deku
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
    Tutu_in_YYC
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      MikhailGG
      Icon for Helper I rankHelper I
      1 year ago

      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.

  • MikhailGG's avatar
    MikhailGG
    Icon for Helper I rankHelper I
    1 year ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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