Error in formula Result within specification

[SanderB]

Dear all,

I have a weird error in PowerBI desktop and I hope someone can help me.

I'm creating a report with laboratory results and I want to add a # before the result is a result is not within specification.

The table contains: Result, Contol high, Control low and I use the following formula:

U_RPT_VALUE_SPECS = IF(RESULT[U_RPT_VALUE]>=RESULT[U_CONTROL_LOW] && RESULT[U_RPT_VALUE]<= RESULT[U_CONTROL_HIGH];RESULT[U_RPT_VALUE];"# "&RESULT[U_RPT_VALUE])

This works very good, except for the 3 last rows in the matrix table.

First column are the specification: Control = RESULT[U_CONTROL_LOW]&"-"& RESULT[U_CONTROL_HIGH]

Second column contains the first set of results, and the second column the second set.

As you can see the last 3 values are not correct interpretated by the formula.

Do I miss something?

Thank in advance

[v-chuncz-msft]

@SanderB,

By the way, you may add conditional column in Query Editor as well.

[SanderB · Accepted solution]

ok, solution was simple.

The result table was set as TEXT and not as Number.

Converted the values to Numbers and it works great.

For someone who needs a script to check if a value is within specification:

U_RPT_VALUE_SPECS = IF(RESULT[U_CONTROL_LOW]="";RESULT[U_RPT_VALUE];(IF(RESULT[VALUE]>=value(RESULT[U_CONTROL_LOW]) && RESULT[VALUE]<= value(RESULT[U_CONTROL_HIGH]);RESULT[U_RPT_VALUE];"# "&RESULT[U_RPT_VALUE])))