Dashboard - filter based on user input, multiple filters

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

???

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:

image

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.

2 Likes

@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) )

image

2 Likes

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

@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) )

1 Like

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. :slight_smile:

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

1 Like

10 plus the row number.

Rows, not columns.

Oops - sorry.
1 per user

1 Like

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?

1 Like

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) )
2 Likes

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

1 Like

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.

1 Like

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)

)