Dynamically change the granularity of the data on the chart

[Anonymous]

Hi,

I have a dataset where the data are measured every hour:

My fact table is connected with the Date dimension (using Date column).

I have a line chart with dynamically changing time perionds - when I select "last 3 days" I only see data for three days, If I select "last 6 days" I see data for 6 days (please see the full solution here: https://community.powerbi.com/t5/Desktop/Changing-date-range-on-the-chart-based-on-slicer-selection/...)

On the X Axis I have Date column from the dimDate table and as a Value I'm using average of factTable[Value] for given day. I want to be able to dynamically change dimDate[Date] to factTable[DateTime]:

If I select "last 3 days" or "Last 6 days" from my slicer I want to see all observation (hour by hour). But when I select longer time period I want to see data aggregated like I have right now (AVG per day).

So for longer periods I want to see chart like this (data are aggregated - AVG per day):

And for shorter periods I want to see not aggregated data:

[v-cherch-msft · Accepted solution]

Hi @Anonymous 

You may try to use measure to get the last n days. For example:

Measure = 
IF (
    MAX ( Table1[Slicer] ) = "Last 3 days"
        && MAX ( 'Fact'[Date] ) < TODAY ()
        && MAX ( 'Fact'[Date] )
            >= TODAY () - 3,
    1,
    IF (
        MAX ( Table1[Slicer] ) = "Last 6 days"
            && MAX ( 'Fact'[Date] ) < TODAY ()
            && MAX ( 'Fact'[Date] )
                >= TODAY () - 6,
        1
    )
)

Regards,