Switch Function

[ajbogle]

Hi guys,

I have this currently working but it does require manual changes if my boss makes any changes to our Matrix. I'd love to automate this but I'm a bit stuck on how I can achieve the same results that I have now.

Currently, this is our Matrix or threshold and based off the Min/Max numbers you are assigned a 'Level' and then it gives you a breakdown of the Headcount needed to run an office.

I'm currently doing a SWITCH measure to get my results, but as you can see in the measure I have to set the parameters manually to match the Matrix.

Current Level = 
SWITCH (
    TRUE(),
    [Projected Installs] >= 0 && [Projected Installs] <= 4, 0,
    [Projected Installs] >= 5 && [Projected Installs] <= 9, 1,
    [Projected Installs] >= 10 && [Projected Installs] < 21, 2,
    [Projected Installs] >= 21 && [Projected Installs] < 41, 3,
    [Projected Installs] >= 41 && [Projected Installs] < 61, 4,
    [Projected Installs] >= 61 && [Projected Installs] < 81, 5,
    [Projected Installs] >= 81 && [Projected Installs] < 101, 6,
    [Projected Installs] >= 101 && [Projected Installs] < 121, 7,
    [Projected Installs] >= 121 && [Projected Installs] <= 140, 8,
    [Projected Installs] >= 141 && [Projected Installs] <= 160, 9,
    28
)

These parameters to determine the level will/have been changed rather consistantly and I'd love to avoid having to adjust them myself manually. Any time I try to bring in the Matrix column [Level] it won't even recognize it since it's not a measure. Thoughts?

Below is a working screenshot of how I am currently getting this model to work and it works well.

[DataInsights · Accepted solution]

@ajbogle,

Try this measure:

Current Level = 
VAR vL1Min = MAXX ( FILTER ( Matrix, Matrix[Level] = 1 ), Matrix[Min - Installs] )
VAR vL1Max = MAXX ( FILTER ( Matrix, Matrix[Level] = 1 ), Matrix[Max - Installs] ) 
VAR vL2Max = MAXX ( FILTER ( Matrix, Matrix[Level] = 2 ), Matrix[Max - Installs] )
VAR vL3Max = MAXX ( FILTER ( Matrix, Matrix[Level] = 3 ), Matrix[Max - Installs] )
VAR vL4Max = MAXX ( FILTER ( Matrix, Matrix[Level] = 4 ), Matrix[Max - Installs] )
VAR vL5Max = MAXX ( FILTER ( Matrix, Matrix[Level] = 5 ), Matrix[Max - Installs] )
VAR vL6Max = MAXX ( FILTER ( Matrix, Matrix[Level] = 6 ), Matrix[Max - Installs] )
VAR vL7Max = MAXX ( FILTER ( Matrix, Matrix[Level] = 7 ), Matrix[Max - Installs] )
VAR vL8Max = MAXX ( FILTER ( Matrix, Matrix[Level] = 8 ), Matrix[Max - Installs] )
VAR vL9Max = MAXX ( FILTER ( Matrix, Matrix[Level] = 9 ), Matrix[Max - Installs] )
VAR vResult =
SWITCH (
    TRUE,
    [Projected Installs] >= 0 && [Projected Installs] <= vL1Min - 1, 0,
    [Projected Installs] >= vL1Min && [Projected Installs] <= vL1Max, 1,
    [Projected Installs] >= vL1Max + 1 && [Projected Installs] <= vL2Max, 2,
    [Projected Installs] >= vL2Max + 1 && [Projected Installs] <= vL3Max, 3,
    [Projected Installs] >= vL3Max + 1 && [Projected Installs] <= vL4Max, 4,
    [Projected Installs] >= vL4Max + 1 && [Projected Installs] <= vL5Max, 5,
    [Projected Installs] >= vL5Max + 1 && [Projected Installs] <= vL6Max, 6,
    [Projected Installs] >= vL6Max + 1 && [Projected Installs] <= vL7Max, 7,
    [Projected Installs] >= vL7Max + 1 && [Projected Installs] <= vL8Max, 8,
    [Projected Installs] >= vL8Max + 1 && [Projected Installs] <= vL9Max, 9,
    28
)
RETURN
vResult

-----

[ajbogle]

Exactly what I needed!