cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
OlafdeWit Frequent Visitor
Frequent Visitor

Show data by different time intervals based on a slicer

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

5 REPLIES 5
Super User
Super User

Re: Show data by different time intervals based on a slicer

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?

OlafdeWit Frequent Visitor
Frequent Visitor

Re: Show data by different time intervals based on a slicer

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

slicer_test_data.PNG

 

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

Community Support Team
Community Support Team

Re: Show data by different time intervals based on a slicer

Hi @OlafdeWit,

What is your desired result?

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Super User

Re: Show data by different time intervals based on a slicer

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 )
    )
OlafdeWit Frequent Visitor
Frequent Visitor

Re: Show data by different time intervals based on a slicer

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.