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
Created a 'Pane for filtering view to be used on the dashboard, with data โFilteredDataForDashboardโ
Created a dashboard called โDashboard for filteringโ and added 2 views
a. Pane for filtering
b. Filters (I think Iโm wrong here).
Would really appreciate your guidance.
Thanks
Solved! Go to 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) )
@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:
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.
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) )
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) )
@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:
Dashboard - Filtered NLOs uses FilteredDataForDashboard
which is a slice of my full data tableโฆ
and applies the filterโฆ
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)
)
User | Count |
---|---|
43 | |
30 | |
24 | |
23 | |
13 |