Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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

Need Help

5 Replies

  • Stachu's avatar
    Stachu
    Icon for Community Champion rankCommunity Champion
    7 years ago

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

      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's avatar
        v-danhe-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        7 years ago

        Hi Anonymous,

        What is your desired result?

         

        Regards,

        Daniel He

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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.