Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

SWITCH with if conditions?

I have a DAX code in which i'm creating % buckets based on ranges of values...the below is creating a bucket based on where each agent falls according to amount of sales:

 

Value =
SWITCH (
TRUE (),
[TMRA2] >= 0
&& [TMRA2] < 16000, .70,
[TMRA2] >= 16000
&& [TMRA2] < 36000, .75,
[TMRA2]>= 36000
&& [TMRA2] < 66000, .80,
[TMRA2]>= 66000
&& [TMRA2] <91000, .85,
.90
)
 
This works, however one agent is 'grandfathered' and has a default percentage of 90% regardless of sales.   I would love to keep them all in one visual instead of creating a seperate bucket that just equals 90%.
 
Is there any way to do this?

3 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    2 years ago

    Put a check for if the agent is grandfathered before any of the other logic.

    SWITCH (
    TRUE (),
    [IsGrandfatheredAgent], .90,
    [TMRA2] >= 0     && [TMRA2] < 16000, .70,
    [TMRA2] >= 16000 && [TMRA2] < 36000, .75,
    [TMRA2] >= 36000 && [TMRA2] < 66000, .80,
    [TMRA2] >= 66000 && [TMRA2] < 91000, .85,
    .90
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Thanks, I can't get that to work though.  I created a column to indicate whether or not agent was grandfathered, but i'm not able to add that to the SWITCH formula

      • AlexisOlson's avatar
        AlexisOlson
        Icon for Super User rankSuper User
        2 years ago

        If you're writing a measure rather than a calculated column, then you can't just use the column, you need some sort of aggregator. For example, assuming you have a 0/1 column in your agent dimension table,

        SWITCH (
    TRUE (),
    SELECTEDVALUE ( dimAgent[IsGrandfathered] ) = 1, .90,
    [TMRA2] >= 0     && [TMRA2] < 16000, .70,
    [TMRA2] >= 16000 && [TMRA2] < 36000, .75,
    [TMRA2] >= 36000 && [TMRA2] < 66000, .80,
    [TMRA2] >= 66000 && [TMRA2] < 91000, .85,
    .90
)