measure difference from filtered values

[Anonymous]

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:

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)

On the graph I see that the measure applies only for one measurement which is actually the reference one => I get 0 everywhere. But it does not work for the other values of the column (other values of other measurements).

I think it might have to do that power BI cannot sort the values in the right order to be able to calculate the requested measure...  I don't know how I can sort, or order or give a condition saying where the baselinevalue[depth] = measurevalue[depth]... 

can someone help me?

Then I need to solve the problem of being able to visualize the measure values on my x-axis and not on the y-axis.

and then I need to figure out how I can choose the reference measurement dynamically... 

I will really appreciate some help!

Thank you in advance.

Best regards,

Yushi

[Anonymous]

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]

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

[Anonymous]

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

[Anonymous]

Thanks a lot for your reply.

It is the exact same Measure that I sued to calculate the displacement as mentioned in my post above. But it is not working for all the values but only for the rows where column reference_measurement = 1.

I even created a calculated column to be able to control the resulted values of my measure as follows: 

We can see in the picture, that column Verschiebung is equal to 0 in the rows where reference_measurement = 1 which means the substraction of the DAX formula is actually working for these rows. But looking at the rows where reference_measurement = 0, I get in column Verschiebung the exact same values that I have in column Value which means that the subtraction that I am trying to execute is not effective for the rows where reference_measurement = 0 and this is my challenge. I want this Dax Formula to be effected for all the rows of the table. 

[Anonymous · Accepted solution]

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]

Thnaks a lot for your help. 

I did not use exactly your solution. But it did inspire me to create my own. 

I used filters and eralier to do it.