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 ( TRU...
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
)