Calculated column not working

[jak8282]

Hi,

For some reason my calculated column is no working - all values are a decimal number.

DAX is 

TestRange70to79.9 = 
IF(
    AND(
        [Supplier Percentage Conv] >= 'Time Penalty'[LeftValue],
        [Supplier Percentage Conv] <= 'Time Penalty'[right position]
    ),
    "Yes", 
    "No"
)

The value of Supplier percentage conv is 77.16 .

It is returning "No" for every row.

See image.

Any help appreciated.

[jak8282]

Hi @Dhairya ,

Sure,

Time Penalty

Percentage	Penalty
90- 97.9	10%
80- 89.9	20%
70 - 79.9	30%
60- 69.9	40%
<60	50%

Calculated Columns

DashPosition = SEARCH("-", TRIM('Time Penalty'[Percentage]), 1, BLANK())

LessThanPosition = SEARCH("<", TRIM('Time Penalty'[Percentage]), 1, BLANK())

LeftValue =

IF(

    NOT ISBLANK('Time Penalty'[DashPosition]),

    VALUE(TRIM(LEFT('Time Penalty'[Percentage], 'Time Penalty'[DashPosition] - 1))),

    BLANK()

)

right position = IF(

    NOT ISBLANK('Time Penalty'[DashPosition]),

    VALUE(TRIM(MID('Time Penalty'[Percentage], 'Time Penalty'[DashPosition] + 1, LEN('Time Penalty'[Percentage]) - 'Time Penalty'[DashPosition]))),

    IF(

        NOT ISBLANK('Time Penalty'[LessThanPosition]),

        VALUE(TRIM(MID('Time Penalty'[Percentage], 'Time Penalty'[LessThanPosition] + 1, LEN('Time Penalty'[Percentage]) - 'Time Penalty'[LessThanPosition]))),

        BLANK()

    )

)

TestRange70to79.9 =

IF(

   [Supplier Percentage Conv] >= 'Time Penalty'[LeftValue] &&

   [Supplier Percentage Conv] <= 'Time Penalty'[right position],

    "Yes",

    "No"

)

and the measure

 [Supplier Percentage Conv] = 77.16

[jak8282]

Hi @Dhairya ,

Thats interesting - I hardcoded the 77.16

In the PBI it is using

Supplier Percentage Conv = [Supplier Percentage]*100

which uses

Supplier Percentage = divide([WFM - Supplier Working Time],[WFM - Supplier Scheduled Time])

[Dhairya]

Though it is hard-coded, I think it should work with measures also.
and instead of creating this measure Supplier Percentage Conv = [Supplier Percentage]*100
you can format your previous measure as a percentage

[Dhairya]

I tried your code it is giving perfect output.

[Dhairya]

TestRange70to79.9 = 
IF(
   [Supplier Percentage Conv] >= 'Time Penalty'[LeftValue] AND
   [Supplier Percentage Conv] <= 'Time Penalty'[right position],
    "Yes", 
    "No"
)

Please try this

[jak8282]

That doesnt work it comes back with the error

The syntax for 'AND' is incorrect. (DAX(IF( [Supplier Percentage Conv] >= 'Time Penalty'[LeftValue] AND [Supplier Percentage Conv] <= 'Time Penalty'[right position], "Yes", "No"))).

I have also tried 

TestRange70to79.9 = 
IF(
   [Supplier Percentage Conv] >= 'Time Penalty'[LeftValue] &&
   [Supplier Percentage Conv] <= 'Time Penalty'[right position],
    "Yes", 
    "No"
)

Which doesnt work either

[Dhairya]

Hey @jak8282 
Can you please share sample data and measures which are used for plotting so that I can try it on my machine?