Excluding negative values from a calculated column

[mshamsiev]

Hi all,

I'm using two columns to determine the mean time to resolve. However, some of the 'resolved' fields are blank resulting in negative calculations for MTR. 

Is there any way to exclude the negatives from the calculations in order to determine an accurate average?

Much appreciated.

Mike 

[prateekraina]

Hi @mshamsiev,

Can you share the formula which you are using?

Prateek Raina

[mshamsiev]

Hi @prateekraina,

Thanks for the reply! I think I may have figured it out.

Using Time to Resolve = IF((OpenInc[Resolved] - OpenInc[Opened])>=0,(OpenInc[Resolved] - OpenInc[Opened].[Date]),BLANK()). 

But now when I attempt to establish a relationship between this table (OpenInc) and the DateKey table, an error arises within the field 'OpenInc' [Time to Resolve]. 

-Mike 

[GilbertQ · Accepted solution]

Hi @mshamsiev

Instead of doing a calculated column, why not use the same syntax and create a measure. Because that is possibly what you want?

Another option which I recommend is to go into the Query Editor and create a conditional column. This is a lot easier as well as has more options to determine your new column.

As well as this will also improve the performance of your Power BI Desktop file.

[mshamsiev]

Hi @GilbertQ,

Cheers for the suggestions! As much as I would love to attempt using conditional columns, I've yet to explore query editor. 

Does it perform in a similar manner to calculated columns?

-Mike 

[GilbertQ]

It is very similar and actually a lot easier to do it in the query editor