Incrementally add fixed value to measure based on other measure

[Anonymous]

Hello,

I have a measure which calculates appointments per hour per employee.

Now I need another measure which calculates a value based on appointments per hour.

For example: If appointments per hour >= 1.5 the value should be 50, if appointments per hour >=1.75 the value should be 100, if appointments per hour >= 2 the value should be 150 and so on.

How can I incrementally add 50 every 0.25 appointements per hour based on my appointmens per hour measure?

Thanks!

[Anonymous]

Hey @Anonymous 

This is neat question. You can do it multiple ways.

The first and most obivous is to create a complicated DAX measure with multiple embedded IF statements.

However, the cooler and overall easier solution is to create a new table with your appointments per hour and your values and then use a LOOKUPVALUE DAX measure: https://docs.microsoft.com/en-us/dax/lookupvalue-function-dax

Your table would look like:

App/Hour    Value

1.50             50

1.75             100

2.00             200

Let me know what you think and what would work better for you.

If this helps please kudo.

If this solves your problem please accept it as a possible solution.

[parry2k · Accepted solution]

@Anonymous add folllowing measure for Value measure

Value Measure = 
VAR __baseHours = 1.5
VAR __baseValue = 50
VAR __denominator = 0.25
VAR __newHours = [Your Appointment Per Hour Measure]
RETURN
(
    FLOOR (
        DIVIDE ( ( __newHours - __baseHours ), __denominator, 0 ),
        1
    ) * 50 
) + 50

.

May be you can tweak it further as per your need.