Creating a calculated boolean field based on thresholds

Question

I'm analysing some data using Power BI, and I have a field in the data called deflection. Normally the value in the field is 0, but a small number of results will have other values ranging from -360 to +360.

Within the dataset, any value > 2 or < -2 might be significant. I would like to create a new boolean value to indicate if the deflection value is significant or not.

• Any value between -2 and + 2 is NOT significant - bSigDeflection = False
• Any other value - bSigDeflection = true

How do I approach this?

I have started by using an abs(deflection) value in the sql query, just to keep everything positive. I could write a sql CASE statement that would probably work but I feel that there must be a simpler way to achieve this in Power BI that I am missing.

Any suggestions, clever people?

Thanks

Answer 1

You may use TransformColumns to replace the value with the bool. Or you can use the same code with Table.AddColumn if you want to keep the original value.

let
   Source = ...,
   coerceDefelectBool = (input) as logical =>
       if input < 2 or input > 2 then true else false,
   Final = Table.TransformColumns(
       Source,
       {"Deflection", coerceDefelectBool},
       null, MissingField.Ignore
   )
in 
    Final

If you wanted a measure, not a DAX calculated column, you can try something like this.

[ bSigDeflection ] = SWITCH (
    TRUE,
    SELECTEDVALUE( [Table].[Deflection] ) > 2, TRUE(),
    SELECTEDVALUE( [Table].[Deflection] ) < 2, TRUE(),
    FALSE()
)