Forum Discussion

stefanycheck's avatar
stefanycheck
Frequent Visitor
8 years ago
Solved

Comparing values in the same column

Hi! 

I'm plotting data from MySQL to analyse data from a temperature sensor and I have a graphic showing its reading, an indicator showing the mean and cards showing the min and the max readings. Sometimes, though, the reading drops or rises too much by a transmission failure and I don't want these reading in my graphic, or in any of the other visuals. So I wanted something to filter the data when the difference between a temperature and the one before it is more than 10 degrees, for example.

In excel, it would be easy to do it, since I can compare cells, but I don't know how to do it in BI. 

 

Here's an example of the data, the column that I want to filter is Temperatura, the other ones you can ignore hahah

 Data

 

  • Anonymous's avatar
    Anonymous
    8 years ago

    Hey stefanycheck

     

    I can't see your data but I have an idea for you. You easily can go in the query editor and add an index column. This will sequentially rank your data and we can use these indexes in a calculated column. Something like:

     

    OutlierColumn =
VAR CurrentIndex = SELECTEDVALUE(Table[Index])
RETURN
IF(
   ABS(
   CALCULATE(
      MAX(Table[Temperature]),
      FILTER(
         ALLSELECTED(Table),
         TableIndex = CurrentIndex
      )
   ) -
   CALCULATE(
      MAX(Table[Temperature]),
      FILTER(
         ALLSELECTED(Table),
         TableIndex = CurrentIndex - 1
      )
   )) >= 10,
   0,
   1
)

    If I wrote that correctly, you are grabbing the current temperature and the temperature of the row above it. You subtract the two and take the absolute value. If the absolute value is greater than or equal to 10, the new column is set to 0. If it is within 10, it is given a 1. You can then bring this new column into the Visual level filters and filter for where OutlierColumn = 1.

     

    Hope this helps!

    Parker

2 Replies

  • jthomson's avatar
    jthomson
    Solution Sage
    8 years ago

    Think you're doing the old thing of posting a link to the page on which the image you want is hosted on, rather than the image itself, can't see anything?

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hey stefanycheck

     

    I can't see your data but I have an idea for you. You easily can go in the query editor and add an index column. This will sequentially rank your data and we can use these indexes in a calculated column. Something like:

     

    OutlierColumn =
VAR CurrentIndex = SELECTEDVALUE(Table[Index])
RETURN
IF(
   ABS(
   CALCULATE(
      MAX(Table[Temperature]),
      FILTER(
         ALLSELECTED(Table),
         TableIndex = CurrentIndex
      )
   ) -
   CALCULATE(
      MAX(Table[Temperature]),
      FILTER(
         ALLSELECTED(Table),
         TableIndex = CurrentIndex - 1
      )
   )) >= 10,
   0,
   1
)

    If I wrote that correctly, you are grabbing the current temperature and the temperature of the row above it. You subtract the two and take the absolute value. If the absolute value is greater than or equal to 10, the new column is set to 0. If it is within 10, it is given a 1. You can then bring this new column into the Visual level filters and filter for where OutlierColumn = 1.

     

    Hope this helps!

    Parker