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.
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
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])
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) )
Created a ‘Filters’ view to show the ‘Filters’ table data to be used on a dashboard
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.
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.
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.
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.
@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.
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.
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’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.
