Restrict number of data points in Power BI visuals

I was recently working on a project where my client had more than 15 years of data. On a very simple report, they had a date range slicer and one bar chart visual – on the bar chart, they had the Date on the x-axis and the Amount showing as a bar. Everything seemed super simple until they mentioned that they didn’t want to show the visual if the user selects more than 24 months in the date range because that makes the graph very crowded. In other words, at any given time they want to see a maximum of 24 months of data.

Problem Statement

If a user selects more than 24 months in the date range, they don’t want to show the bar graph visual and want to give a message to the user to shorten the date range.

The first approach that comes to mind is to filter the data in Power Query or at the data source to load data for only the last 24 months. This will work – somewhat; it will restrict the users to work only with data from the last 24 months and they cannot go beyond that. Here is how I approached the problem to keep all 15 years of data, but only show data for 24 months at any given time.

Let’s take a look at our sample data first. It is pretty straight forward and has a Date and Amount column.

For this blog post, instead of using a fixed restriction for 24 months I added a what-if parameter that allows the user to select how many months of data they want to see. The what-if parameter makes it more dynamic (although that was not a requirement) and show data based on number of months selected in what-if parameter.

Learn more about what-if parameters here.

I also made the what-if parameter range dynamic based on the number of months in the Calendar table. I achieved this by using the following DAX expression:

Max # of months to show =
VAR __startDate = MIN (  'Calendar'[Date] )
VAR __endDate = MAX ( 'Calendar'[Date] )
VAR __maxMonths = DATEDIFF ( __startDate, __endDate, MONTH ) + 1
RETURN
    GENERATESERIES (1, __maxMonths, 1 )

Now on my report page, I have a what-if slicer, a Date range slicer, and a bar graph.

As you can see in the above video, if the number of months in the date range is more than the number of months selected in what-if parameter, user gets a message otherwise the visual is shown.

To make this all work, I added few measures. I added a measure called Number of months selected and it basically calculates how many months the user has selected in the date range slicer.

Number of months selected =
VAR __dateRange = ALLSELECTED ( 'Calendar'[Date] ) --get selected date range
VAR __minDate = CALCULATE ( MIN ( 'Calendar'[Date] ), __dateRange )   --get first date in the date range
VAR __maxDate = CALCULATE ( MAX ( 'Calendar'[Date] ), __dateRange )   --get last date in the date range
RETURN
--return # of months selected
    DATEDIFF (
        __minDate,
        __maxDate,
        MONTH
    )

Here is a measure to check if Number of months selected is more than Max # of months (what-if parameter value):

Out of range check =
[Number of months selected] <= [Max # of months to show Value]

The Total Amount measure returns blank value if Number of months selected is more than what-if parameter value, otherwise it simply returns the SUM of Amount:

Total Amount =
VAR __selectedVsAsked = [Out of range check]
RETURN
SUM ( Data[Amount] ) * DIVIDE ( __selectedVsAsked, __selectedVsAsked )

We have another measure to show a warning message if we are outside the range:

Out of range message =
IF (
    [Out of range check],
    "",
    "Selected date range should be less than max months to show"
)

and that’s it!

To make it all work, I added a Bar Graph visual with the Date on the x-axis and the Total Amount measure in values. I also added a Card visual and used the Out of range message measure as the value. In the selection pane, make sure that your card visual is before the bar chart visual in layer order (see below).

I hope you can also take advantage of this technique and can restrict your visuals to show x number of data points in order to provide more insights with less crowded visuals.

Looking forward to your comments and feedback – and if you have come across a similar requirement, how did you solve it?