SWITCH with if conditions?

[paguy215]

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?

[AlexisOlson]

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
)

[paguy215]

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]

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
)