Forum Discussion

sigtopo's avatar
sigtopo
Frequent Visitor
3 years ago
Solved

Average for mesure with date slicer

I have a dataset with the following:

  • id_sensor: sensor identifier
  • measurement_date: measurement date
  • data_value: value

I want to represent average values over a date range that the Powerbi dashboard user can select with a slicer.
I am in directQuery mode on a Postgresql database.
Is it possible ?

Here is a sample of data

id_sensormeasurement_datedata_value
1202402/06/2023 17:210.5
1202422/05/2023 17:020.25
1202423/05/2023 10:250.25
1202401/06/2023 16:580.5
1202415/05/2023 06:440
1202415/05/2023 17:040.5
1202416/05/2023 07:110.25
1202417/05/2023 07:160.25
1202419/05/2023 07:420
1202419/05/2023 17:181.25
1202420/05/2023 07:191
1202410/06/2023 16:260.5
1202411/06/2023 06:561.25
1202412/06/2023 14:501.25
1294622/05/2023 18:290.25
1294623/05/2023 06:190.25
1294603/06/2023 14:520.5
1294624/05/2023 13:490.25
1294605/06/2023 14:030.5
1294625/05/2023 15:520.5
1294606/06/2023 14:090.75
1294627/05/2023 15:081

....

average
date slicer
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    Hi, sigtopo 

     

    You can try the following methods. This formula can be run in Direct query mode.

    Measure =
AVERAGEX ( FILTER ( 'Table',
        [measurement_date] >= MIN ( 'Table'[measurement_date] )
            && [measurement_date] <= MAX ( 'Table'[measurement_date] )
            && [id_sensor] = SELECTEDVALUE ( 'Table'[id_sensor] )
    ),
    [data_value]
)

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • v-zhangti's avatar
    v-zhangti
    Community Support
    3 years ago

    Hi, sigtopo 

     

    You can try the following methods. This formula can be run in Direct query mode.

    Measure =
AVERAGEX ( FILTER ( 'Table',
        [measurement_date] >= MIN ( 'Table'[measurement_date] )
            && [measurement_date] <= MAX ( 'Table'[measurement_date] )
            && [id_sensor] = SELECTEDVALUE ( 'Table'[id_sensor] )
    ),
    [data_value]
)

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.