Forum Discussion

jak8282's avatar
jak8282
Helper III
3 years ago

Calculated column not working

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.

Need Help

7 Replies

  • Dhairya's avatar
    Dhairya
    Solution Supplier
    3 years ago
    TestRange70to79.9 = 
IF(
   [Supplier Percentage Conv] >= 'Time Penalty'[LeftValue] AND
   [Supplier Percentage Conv] <= 'Time Penalty'[right position],
    "Yes", 
    "No"
)

    Please try this

     

    • jak8282's avatar
      jak8282
      Helper III
      3 years ago

      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's avatar
        Dhairya
        Solution Supplier
        3 years ago

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

  • jak8282's avatar
    jak8282
    Helper III
    3 years ago

    Hi Dhairya ,

     

    Sure,

     

    Time Penalty

    PercentagePenalty
    90- 97.910%
    80- 89.920%
    70 - 79.930%
    60- 69.940%
    <6050%

     

    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
     
    • Dhairya's avatar
      Dhairya
      Solution Supplier
      3 years ago

      I tried your code it is giving perfect output.

    • jak8282's avatar
      jak8282
      Helper III
      3 years ago

      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's avatar
        Dhairya
        Solution Supplier
        3 years ago

        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