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,

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's avatar
Anonymous
Not applicable
5 years ago

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's avatar
    Anonymous
    Not applicable
    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

      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.