topic Re: SWITCH with if conditions? in DAX Commands and Tips

SWITCH with if conditions?

Anonymous — Tue, 13 Feb 2024 14:47:39 GMT

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?

Re: SWITCH with if conditions?

AlexisOlson — Tue, 13 Feb 2024 16:39:53 GMT

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 )

Re: SWITCH with if conditions?

Anonymous — Tue, 13 Feb 2024 17:10:46 GMT

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

Re: SWITCH with if conditions?

AlexisOlson — Tue, 13 Feb 2024 18:19:43 GMT

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 )