Forum Discussion
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 measurement with a reference measurement that can be chosen dynamically.
Every ONE measurement is composed of 40 rows, depending on the depth and the direction A or B.
All 40 rows are to be recognized by the same measurement date and time... This is how a measurement is defined.
As a first step. I have fixed a reference measurement by adding a column called reference_measurment which is equal to 1 in the rows to be considered for this reference measurement and is then 0 elsewhere.
I have created a quick measure as follows:
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
6 Replies
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
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
Hi Anonymous,
Ok, I think they are clear enough to help us understand these requirements and data structures.
According to your description, it seems like you want to show the date range based on the measurement selected in the slicer, right? (the remain time values of selected date)
If that is the case, I'd like to suggest you create an unconnected parameter table with all measurement values and use its field as the source of a slicer.
After these steps, you can write create a measure formula to extract the selected value 'datetime' part and use on 'visual level filter' of the chart to keeping records of corresponding date values.measure = VAR currDate = MAX ( Table[Date] ) VAR select = SELECTEDVALUE ( NEWTable[Messung] ) VAR _date = DATEVALUE ( SUBSTITUTE ( selected, "Messung", "" ) ) RETURN IF ( currDate >= _date, 1, 0 )For the chart design, you can create a line chart with 'date' value as axis, sensor/depth as legend, and expression formula on the value field. (since chart not able to use multiple legend fields, you can add a slicer with 'direction' filed to choose control which direction displayed)
Average of value difference from 1 = IF ( AVERAGE ( 'Table'[value] ) <> BLANK (), AVERAGE ( 'Table'[value] ) - CALCULATE ( AVERAGE ( 'Table'[value] ), 'Table'[reference_measurement] = 1 ) )Regards,
Xiaoxin Sheng