Switch Statement?


#1

Have a chart here of job types, margins & tier. Wondering if best option is to create a Switch statement or some other form of DAX. Any suggestions are welcome. Thanks in advance.


#2

With the information you’ve provided I’m struggling to understand what you actually need.

Can you provide far more information.

What are you actually looking to achieve??


#3

I was trying to figure out a calculation that would apply the correct tier for each job type according to the margin range. After a bit of thinking I was able to figure it out. I first created measures for margin for each job type, then created measures that would apply the correct tier based on margin range. Here are examples of both measures:

STR Incentive Margin =
CALCULATE (
    [Margin%],
    FILTER ( Job_Mgmt_Report, Job_Mgmt_Report[IncentiveJobType] = "STR" )

STR Incentive Tier =
IF (
    [STR Incentive Margin] >= .49
        && [STR Incentive Margin] <= .51,
    1,
    IF (
        [STR Incentive Margin] > .51
            && [STR Incentive Margin] <= .53,
        2,
        IF (
            [STR Incentive Margin] > .53
                && [STR Incentive Margin] <= .55,
            3,
            IF (
                [STR Incentive Margin] > .55
                    && [STR Incentive Margin] <= .57,
                4,
                IF ( [STR Incentive Margin] > .57, 5, 0 )
            )
        )
    )
)

Next time will try to give more information to make it easier for you to reply. Thanks again for your response!


#4

Side note, see here for posting measures into forum - How Place DAX Formula Into Forum Topics & Posts


#5

Ok that’s great. Glad you got it done. Chrs