Reduce number of line chart points when using live dataset?

[lehikk]

I have a Live Dashboard with some Gauges and Cards embedded from a report. In other words I'm not using those "limited" live data visuals but some others which I've set up on PBI Desktop, then published to the Service and finally embedded them to the Live Dashboard.

There are a few dozen measurements coming in from a Live Dataset every minute and the Gauges and Cards work just fine. However, I would like to have a line chart that shows six spesific column values from a one week timespan. The problem here is that the amount of data generates over 60000 points to the chart, which a bit heavy. For example, the hover popup is quite slow and filtering takes pretty long. As I'm using a live dataset, I can't use Power Query to transform the data. Also, the "limited" Live version of a line chart would only show maximum of last 60 minutes, so it's not an option here. Would it be possible to use DAX measure to get for example the latest value and let's say one value per hour for previous week? 

[v-cazheng-msft · Accepted solution]

Hi, @lehikk 

I'm a little bit confused about your needs. Maybe you need a Measure like this?

Last sixty minutes =

VAR now =

    NOW ()

VAR cur_year =

    YEAR ( now )

VAR cur_mon =

    MONTH ( now )

VAR cur_day =

    DAY ( now )

VAR cur_min =

    MINUTE ( now )

VAR select_date =

    CALCULATE (

        SELECTEDVALUE ( 'Table'[Date] ),

        FILTER (

            'Table',

            YEAR ( 'Table'[Date] ) = cur_year

                && MONTH ( 'Table'[Date] ) = cur_mon

                && DAY ( 'Table'[Date] ) = cur_day

                && (

                    cur_min - MINUTE ( 'Table'[Date] ) <= 60

                )

        )

    )

RETURN

    select_date

Actually, from December 2020, Desktop allows change the connection mode with live datasets from live connection to Direct Query and releases some limitations with live datasets. You can try it.

Best Regards,

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lehikk]

Sorry it took a bit long to get back to the topic. To clarify, let's put it this way:

I have live dataset where a new value is added every minute. I want to show data for the last week which equals arount 10000 values (60*24*7). The problem is that such number of points is an overkill as I would need to see only the newest value and hourly average values for the last week. So basically I'm asking, is it possible to create aggregates in such live data scenario.

(the example is oversimplified and in real life the would be at least 60000 - 100000) values for the given timeframe, which tends to be quite heavy when viewed as a Line Chart in browser inside Power BI Service, especially when hovering over the chart)