Blogs

Power BI > How to filter all the data by TODAY, by default?

Inkey, September 10, 2021 20832 Views

Ever wondered how we can filter the Power BI report to show only Today’s data by default but at the same time give the flexibility to change the Date filter?

The approach we as developers generally take is – create a calculated column IsToday in the Calendar table and have it return “Yes” for today’s date and “No” for all other dates and then apply the filter selecting “Yes” to show today’s data by default. The problem with this approach is, when filtered on “Yes”, it also filters the Data slicer to showcase only 1 date, i.e. Today in this case, and in order to change the Date filter, the user first needs to set the IsToday filter to “All”. But now, we can easily achieve this with more flexibility using Calculation Groups.

This is how our final outcome will look like:

By default, here we are filtering the data by today’s date. So, whenever a user opens the report, he would be able to see only today’s data and if he filters the date, he would be able to see the data falling within the selected date range.

 

---

Discover the power of our services:

Microsoft Fabric, Power BI, Microsoft Business Intelligence, SQL Server, and Business Central. By the power of these services, from advanced analytics to seamless business integration, we’ve got the expertise you need to optimize operations and drive growth. Harness the potential of your data infrastructure with our comprehensive suite of solutions.

---

 

With these expectations set, let’s jump in to understand the steps we need to follow to create the calculation group for the default values. To achieve the above requirement, we need Tabular Editor to be installed on the machine. If you haven’t downloaded Tabular Editor yet, please click here to download the latest version.

Now, let’s move to the Power BI report. Open Power BI Desktop & go to External Tools and open Tabular Editor.

Once you open the Tabular Editor, go to the left panel where you can right-click on the Tables >
Create New > Calculation Group or you can directly press Alt+7.

Now, modify the name of the Calculation Group. Here, we have defined it as FilterByToday.

After creating the calculation group, expand it and you can see there is a column called Name. We’ll modify the name as per our requirements to display the column in Power BI and so we will change it to Options.

Now, let’s create the two calculated items, 1) NO and 2) YES.

The idea is, when filter is applied on NO, users would be able to see all data (here, as per the filtered date range in the Date Slicer). And when the filter is applied on YES, users would be able to see data as per the default values (here, today’s data), and if the date range from the slicer is changed, the data would be filtered as per the date range, giving it the priority.

To create the calculation items right click on the calculation items and select New Calculation Item.
For the NO, let’s define the calculation as follows:

NO = SELECTEDMEASURE()

NO will do nothing. It will not apply any filter.  It is used by expressions for calculation items to reference the measure that is currently in context.

Now, let’s create YES and define the below calculation as follows:

YES =
IF ( 
    CALCULATE ( 
        ISCROSSFILTERED ( 'DATE' ) , 
        ALLSELECTED() 
    ) , 
    SELECTEDMEASURE() ,
     CALCULATE ( 
        SELECTEDMEASURE() , 
        FILTER ( 'DATE' , 'DATE'[DATE] = TODAY () ) 
    ) 
)

So, the measure here is really simple. If the date range is filtered in the slicer or is cross filtered, it will return SELECTEDMEASURE(), i.e the measure without any contextual filters, and if the date slicer is not filtered, SELECTEDMEASURE() with the TODAY() filter is returned.

After creating both the calculation items, let’s save the report to see the change in the Power BI report. Now, close the Tabular Editor and go back to the report. In the table section, you can see
the FilterByToday table with column Options. 

Now, plot the Options in the filter pane and select YES as shown below. After applying the filter, hide and lock the filter. Now, by default you would be able to see today’s data and the working will be as under –

This approach opens up a wide range of possibilities. This is what we have tried. If you can come up with another such cool trick, please do share the link in the comments below.

Happy Default-Filtering!