Show data by different time intervals based on a slicer

[Anonymous]

Hi all,

In my report I have a graph with datetime on the x-axis. The parameter values come in by the minute, so I'm using Direct Query to show the latest data.

Now I want to be able to switch between showing (aggregated) data per 1min, 5mins, 15mins and 60mins, based on a slicer in the report.

Can anybody tell me how to do this, especially since DAX functionality is limited in Direct Query?

Thanks, br.

Olaf

[Anonymous]

In the end we solved this by adapting the underlying datatable:

- Added a column "interval" with four values, one for each required interval (1, 5, 15, 60)

- Every time corresponding to a 5, 15 or 60 minute interval, we added a row with the calculated averag over taht interval, for each parameter

Now we simply used the "interval column in a slicer.

Not the most "clean" solution, but it works for our use case.

Next case though we will try Stachu's code.

[Stachu]

SWITCH should be a possibility, but maybe there is more performant solution

can you share the anonymised sample of your data? if you already have the measures showing the individual aggregates, can you share the syntax?

[Anonymous]

The data looks something like this, only with around 40 parameters and a new row being added every minute.

The processing is done before Power BI, so the aggregation would be done there.

[v-danhe-msft]

Hi @Anonymous,

What is your desired result?

Regards,

Daniel He

[Stachu]

try this code - I assume you already have a slicer that has numeric values 1,5,15,60

Measure =
VAR Minutes =
    MAX ( Slicer[SlicerValue] )
RETURN
    CALCULATE (
        SUM ( Table[dT] ),
        Table[DateTime]
            >= TODAY () + NOW () - Minutes / ( 24 * 60 )
    )