cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## 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.

7 REPLIES 7
Helper III

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

Helper III

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])

Super User

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

Super User

I tried your code it is giving perfect output.

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

Helper III

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

Super User

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.