topic Re: Average for mesure with date slicer in DAX Commands and Tips

Average for mesure with date slicer

sigtopo — Tue, 13 Jun 2023 13:55:13 GMT

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_sensor	measurement_date	data_value
12024	02/06/2023 17:21	0.5
12024	22/05/2023 17:02	0.25
12024	23/05/2023 10:25	0.25
12024	01/06/2023 16:58	0.5
12024	15/05/2023 06:44	0
12024	15/05/2023 17:04	0.5
12024	16/05/2023 07:11	0.25
12024	17/05/2023 07:16	0.25
12024	19/05/2023 07:42	0
12024	19/05/2023 17:18	1.25
12024	20/05/2023 07:19	1
12024	10/06/2023 16:26	0.5
12024	11/06/2023 06:56	1.25
12024	12/06/2023 14:50	1.25
12946	22/05/2023 18:29	0.25
12946	23/05/2023 06:19	0.25
12946	03/06/2023 14:52	0.5
12946	24/05/2023 13:49	0.25
12946	05/06/2023 14:03	0.5
12946	25/05/2023 15:52	0.5
12946	06/06/2023 14:09	0.75
12946	27/05/2023 15:08	1

....

Re: Average for mesure with date slicer

v-zhangti — Thu, 15 Jun 2023 06:31:52 GMT

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.

Re: Average for mesure with date slicer

sigtopo — Thu, 15 Jun 2023 12:26:10 GMT

It's perfect ! Thanks !