Associating User Properties with Data Filtered on a Dashboard

There may be cases when you place multiple views on a dashboard, and a current user needs to filter them by the same criteria simultaneously.

To create such interactive dashboards, you can associate specific data with a particular user via the User Property Table. The best part is that you have access to any column from a record related to a current user in any formula without creating any relations. This ability allows you to pass parameters to any formula column or filter condition, which can be useful for various scenarios.

For example, let’s say you have placed the "Monthly Sales," "Quarterly Sales," and "State Sales" views on the dashboard. Each view includes a Date column, and you want to filter the data by From and To date criteria in all three views simultaneously. First, you should create such a filter as a new table view of the User Property Table. This view will allow modification of the current user properties directly on the dashboard. After that, you can include the following filter condition in the "Monthly Sales," "Quarterly Sales," and "State Sales" views:

Between([Date], User[From], User[To])

So each time you change the "From" / "To" values in the Filter, the system will update the corresponding current user property and reload all views on the dashboard with a new set of data.

Dashboard Filter2

To create the interactive Dashboard Filter, follow these steps:

  1. If your database does not include the User Property Table, you need to create it.

  2. Create the "From" and "To" date columns in the User Property Table.

  3. Next, create a table view in the User Property Table and name it "Dashboard Filter."

  4. Edit the view settings: check the Custom Formula option and add the formula [User]=User().
    Filter View

  5. Then, mark the Custom Columns option and add the "From" and "To" columns to this view.
    Filter View Columns

  6. Create the Table View Button and name it "Change filter." Edit button settings, check the Allow users to edit column(s) option, and select the "From" and "To" columns. This button should be displayed only in the "Dashboard Filter" view.

  7. Edit the settings of the "Monthly Sales," "Quarterly Sales," and "State Sales" views and add the following custom formula to the Filter section: Between([Date], User[From], User[To]).
    Filter-in-the-Dashboards-View.png

  8. Place the "Dashboard Filter" view on the dashboard.

As a result, to specify the "From" and "To" date criteria, click on the "Change filter" button in "Dashboard Filter" and enter the values you need. These criteria will be applied to the "Monthly Sales," "Quarterly Sales," and "State Sales" views.

Using the above-mentioned steps, you can add other filter columns like Record Owner, etc.