Dashboard - filter based on user input, multiple filters

Hey everyone,
Spent the weekend trying to get this to work with no luck so would really appreciate your help. I’m trying to get a view of my data where the user can slice the data based on a number of filters that can be selected from dropdown options. I tried to solve via the help articles on slices based on user data and think I might be close.

  1. I have a table called ‘New Legal obligations’ that has columns included such as Region (lookup based on country), Status (enum), Team name (enum) etc. I’d like to see a page where I can have the data on the left and filters for user to select on the right

  2. I created a ‘filters’ table that includes the column header and the values from the 3 columns (status, region, team name, set as enumlist with validity as SORT(New Legal Obligations[Status])

  3. I took a slice of the ‘New Legal Obligations’ table, called it ‘FilteredDataForDashboard’ and added a row filter condition:
    AND(
    if(isnotblank(Filters[Team Name]), in([Team Name], Filters[Team Name]), True),
    if(isnotblank(Filters[Region]), in([Region], Filters[Region]), True),
    if(isnotblank(Filters[Status]), in([Status], Filters[Status]), True) )

  4. Created a ‘Filters’ view to show the ‘Filters’ table data to be used on a dashboard

  5. Created a 'Pane for filtering view to be used on the dashboard, with data ‘FilteredDataForDashboard’

  6. Created a dashboard called ‘Dashboard for filtering’ and added 2 views
    a. Pane for filtering
    b. Filters (I think I’m wrong here).

  1. When opening the ‘dashboard for filtering’ from the menu view, I get the filters view to be details rather than the option to select which filter to apply to the data slice.

Would really appreciate your guidance.
Thanks

Solved Solved
0 20 4,194
1 ACCEPTED SOLUTION

Use like this:

AND(
if(isnotblank(FiltersForCurrentUser[Team Name]), in([Team Name], FiltersForCurrentUser[Team Name]), True),
if(isnotblank(FiltersForCurrentUser[Region]), in([Region], FiltersForCurrentUser[Region]), True),
if(isnotblank(FiltersForCurrentUser[Status]), in([Status], FiltersForCurrentUser[Status]), True),
if(isnotblank(FiltersForCurrentUser[Priority]), in([Priority], FiltersForCurrentUser[Priority]), True),
if(isnotblank(FiltersForCurrentUser[Obligation Categories]), in([Obligation Categories], FiltersForCurrentUser[Obligation Categories]), True),
if(isnotblank(FiltersForCurrentUser[Primary Jurisdiction]), in([Primary Jurisdiction], FiltersForCurrentUser[Primary Jurisdiction]), True),
if(isnotblank(FiltersForCurrentUser[Applicable Countries]), in([Applicable Countries], FiltersForCurrentUser[Applicable Countries]), True),
if(isnotblank(FiltersForCurrentUser[Products Impacted]), in([Products Impacted], FiltersForCurrentUser[Products Impacted]), True) )

View solution in original post

20 REPLIES 20

@Steve Would you mind taking at look here? Thanks!

Steve
Participant V

???

On the screenshot under bullet 6, the right hand view of the dashboard shows values rather than the ability to select what filters I’d like to apply to my data.
Here I want to be able to see options to pick a region from a dropdown, pick a team name, etc, and have that apply the filter to the data.

You’ll want a detail view that displays only the filter-input columns. Each filter-input columns should be configured as Quick edit columns:

3X_5_4_540422f546805af41a85e03af7f5e42dd9ddf8b7.png

Note that in order to use a detail view, a row in the filter table must already exist for the detail view to display. My approach would be to find a way to automatically create one row per user in the filter-input table, then use a slice to only include the current user’s row, then put the detail view atop that slice.

Worked perfectly!! Thank you, @Steve
Soooo good.

@Steve Thank you - almost working now.

  1. How would I edit my row filter for the slice in order to add the useremail column in the filters table to match useremail() ?

AND(
if(isnotblank(Filters[Team Name]), in([Team Name], Filters[Team Name]), True),
if(isnotblank(Filters[Region]), in([Region], Filters[Region]), True),
if(isnotblank(Filters[Status]), in([Status], Filters[Status]), True),
if(isnotblank(Filters[Status]), in([Status], Filters[Status]), True) )

  1. I have a sync enabled after each form edit, is there a way to turn this off for this dashboard view? Every time I change a filter field, it will save the app and then refresh the slice.

Thanks

AND(
(useremail() = [useremail]),
if(isnotblank(Filters[Team Name]), in([Team Name], Filters[Team Name]), True),
if(isnotblank(Filters[Region]), in([Region], Filters[Region]), True),
if(isnotblank(Filters[Status]), in([Status], Filters[Status]), True),
if(isnotblank(Filters[Status]), in([Status], Filters[Status]), True) )

3X_0_a_0a1825b43e69b889e18ac7a5c4039a9975a2507b.png

@Steve, 1 last question! I don’t think I have the filter set up correctly. Ideally I’d have it so that if all the filters on the right hand side of the picture below are empty, all values are returned (so no data is filtered out).

If in this example, status has ‘confirmed’, then I’ll only like to see those with status ‘confirmed’ which is not the case on the data. Note some filters have multi-select options, such as Region.

Here was the filter I was using for the slice;

AND(
if(isnotblank(Filters[Team Name]), in([Team Name], Filters[Team Name]), True),
if(isnotblank(Filters[Region]), in([Region], Filters[Region]), True),
if(isnotblank(Filters[Status]), in([Status], Filters[Status]), True),
if(isnotblank(Filters[Priority]), in([Priority], Filters[Priority]), True),
if(isnotblank(Filters[Obligation Categories]), in([Obligation Categories], Filters[Obligation Categories]), True),
if(isnotblank(Filters[Primary Jurisdiction]), in([Primary Jurisdiction], Filters[Primary Jurisdiction]), True),
if(isnotblank(Filters[Applicable Countries]), in([Applicable Countries], Filters[Applicable Countries]), True),
if(isnotblank(Filters[Products Impacted]), in([Products Impacted], Filters[Products Impacted]), True) )

First, just FYI, expressions like this:

if(isnotblank(Filters[Team Name]), in([Team Name], Filters[Team Name]), True),

could instead be written like this:

or(isblank(Filters[Team Name]), in([Team Name], Filters[Team Name])),

Slightly shorter, perhaps more aesthetically pleasing.

Second: how many rows are there in the Filters table/slice?

10 plus the row number.

Rows, not columns.

Oops - sorry.
1 per user

That being the case, wherever you reference the Filters table in your expressions, you need to narrow the expression to only use the row for the current user. If you don’t, the expression is considering all users’ filters, not just the current user’s.

For instance, this:

if(isnotblank(Filters[Team Name]), in([Team Name], Filters[Team Name]), True)

would need to be this (indented for clarity):

if(
  isnotblank(
    select(
      Filters[Team Name],
      (useremail() = [useremail])
    )
  ),
  in(
    [Team Name],
    select(
      Filters[Team Name],
      (useremail() = [useremail])
    )
  ),
  True
)

Obviously, this is very cumbersome, and inefficient as well. Better, create a slice, perhaps called My Filter, on the Filters table, with a row filter expression of:

(useremail() = [useremail])

Then reference the My Filter slice in your expression rather than Filters:

if(isnotblank(My Filter[Team Name]), in([Team Name], My Filter[Team Name]), True)

I believe? I have that set up…

My dashboard has 2 views:

  1. Dashboard - Filtered NLOs
  2. Filters for CurrentUser

Dashboard - Filtered NLOs uses FilteredDataForDashboard

which is a slice of my full data table…

and applies the filter…

  1. Filters for CurrentUser is based on FiltersForCurrentUser slice…

where the email in the in the Filters table = useremail()

I’m able to test that the logged in user is updating their own row in the filters table, but I think I’m missing the email filter on the slice, yeah?

Clarification - missing the email filter on FilteredDataForDashboard slice?

Use like this:

AND(
if(isnotblank(FiltersForCurrentUser[Team Name]), in([Team Name], FiltersForCurrentUser[Team Name]), True),
if(isnotblank(FiltersForCurrentUser[Region]), in([Region], FiltersForCurrentUser[Region]), True),
if(isnotblank(FiltersForCurrentUser[Status]), in([Status], FiltersForCurrentUser[Status]), True),
if(isnotblank(FiltersForCurrentUser[Priority]), in([Priority], FiltersForCurrentUser[Priority]), True),
if(isnotblank(FiltersForCurrentUser[Obligation Categories]), in([Obligation Categories], FiltersForCurrentUser[Obligation Categories]), True),
if(isnotblank(FiltersForCurrentUser[Primary Jurisdiction]), in([Primary Jurisdiction], FiltersForCurrentUser[Primary Jurisdiction]), True),
if(isnotblank(FiltersForCurrentUser[Applicable Countries]), in([Applicable Countries], FiltersForCurrentUser[Applicable Countries]), True),
if(isnotblank(FiltersForCurrentUser[Products Impacted]), in([Products Impacted], FiltersForCurrentUser[Products Impacted]), True) )

OH.HECK.YES!
Thank you, @Steve!

I’m currently having trouble with this formula and similar versions of it. In both cases, the filtered view for the current user remains blank until I get very specific with the filters.

Then your expression is probably wrong.

It would be the case but the formula was working fine last week until now. Nothing has changed. It just stopped working. It did work when I only used a portion of it to filter only one column, but it only displays entries when the filter is filled. No results appeared when the filter is blank. Below are both versions of the formulas.

And(

or(isblank(Filter Table[Insurance Filter]),in([Insurance],Filter Table[Insurance Filter])),

or(isblank(Filter Table[Line Item Filter]),in([Line Item],Filter Table[Line Item Filter])),

or(isblank(Filter Table[Project Type]),in([Project Type],Filter Table[Project Type])),

or(isblank(Filter Table[Installer]),in([Caregiver/Installer],Filter Table[Installer])),

or(isblank(Filter Table[Status]),in([Status],Filter Table[Status])),

or(isblank(Filter Table[Region]),in([Region], Filter Table[Region])),

or(isblank(Filter Table[City]),in([City],Filter Table[City]))

)


And(

if(isnotblank(Filter Table[Insurance Filter]), in([Insurance], Filter Table[Insurance Filter]), True),

if(isnotblank(Filter Table[Line Item Filter]), in([Line Item], Filter Table[Line Item Filter]), True),

if(isnotblank(Filter Table[Project Type]), in([Project Type], Filter Table[Project Type]), True),

if(isnotblank(Filter Table[Installer]), in([Caregiver/Installer], Filter Table[Installer]), True),

if(isnotblank(Filter Table[Status]), in([Status], Filter Table[Status]), True),

if(isnotblank(Filter Table[Region]), in([Region], Filter Table[Region]), True),

if(isnotblank(Filter Table[City]), in([City], Filter Table[City]), True)

)

Top Labels in this Space