Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

measure difference from filtered values

Hello Community,   I am trying to visualize an inclinometer chain composed of 20 sensors.  I receive the data every hour and I have to visualize a comparison (difference of values) of every measur...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    HI Anonymous,

    Maybe you can try to use the following calculate column format to replace your expression.

    Calculate column =
CALCULATE (
    AVERAGE ( 'Table'[value] )
        - CALCULATE ( AVERAGE ( 'Table'[value] ), 'Table'[reference_measurement] = 1 ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[last_measurement] = EARLIER ( 'Table'[last_measurement] )
            && 'Table'[sensor] = EARLIER ( 'Table'[sensor] )
    )
)

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Anonymous,

Can you please share some dummy data(keep raw table scheme) and expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hello Xiaoxin Sheng,

First of all, thank you for your concern.

I will try my best to show some data.

As already mentioned, I am working with sensors data. I receive new data every hour.

I am working with a chain consisting of 20 sensors. Every sensor gives me a value in 2 directions A and B. Therefore every ONE measurement consists of 40 rows, that are filtered by their timestamp (date and time), column Messung in the table below. I have represented only 3 measurements with 3 sensors each instead of 20 sensors each as an example (18 rows instead of 120 rows).

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

I have come to visualize every measurement in every direction A and B(2 separate graphs) depending on their depth. The end result is a curve showing the inclination of a wall.

Now, I need to visualize the displacement of the chain compared to the reference measurement that is marked as 1 in the column reference_measurement

So for example when I select Messung 5-7-2021 13:00:00  from my slicer, I want to visualize the difference between Value of Messung 5-7-2021 13:00:00 – Value of the reference measurement which is defined here as the Messung 5-7-2021 12:00:00 PM

When I select Messung 5-7-2021 14:00:00  from my slicer, I want to see the difference between Value of Messung 5-7-2021 14:00:00 – Value of the reference measurement which is defined here as the Messung 5-7-2021 12:00:00 PM in the reference_measurement column

 

I have created the Measure as follows:

Average of value difference from 1 =

VAR __BASELINE_VALUE =

     CALCULATE(

       AVERAGE('Inklinometer (2)'[value]),

        'Inklinometer (2)'[reference_measurement] IN { 1 }

    )

VAR __MEASURE_VALUE = AVERAGE('Inklinometer (2)'[value])

RETURN

    IF(NOT ISBLANK(__MEASURE_VALUE), __MEASURE_VALUE - __BASELINE_VALUE)

The first issue I have with the Measure is:

  • I cannot visualize the Measure on my x-axis. I need to show the curve in accordance to the depth that should be shown on the y-axis.
  • Second, when I check the values of my Measure for any measurement, I receive the same values that I have in my table under column value. Which means the measure was not effective and I do not get any “calculated” values…
  • When I check the “calculated” values of the reference measurement Messung 5-7-2021 12:00:00 PM then I have 0 for all rows, which means the measure was actually effective but only on this one measurement.

My next step would be to dynamically fix the reference measurement by choosing it from a slicer. Any idea if this would be possible?

I hope this is clear. For any additional clarifications, I am at your disposal.

Best Regards,

Yushi