cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
KimberlyM
Level: Powered On

Further Drill Down Filtering

My goal is to be able to display Status="Not Started", Status="In Progress", Status="Dead Deals", etc. based on button click. 

On my Screen my OnVisible property is set to filter already to pull data for my pie chart:

If(!IsEmpty(Filter(Admin_LeadData,FullName=varUser)),
     ClearCollect(colDash_StatusTrack,{Status:"Not Started",Count:CountIf(Lead_Data,Status.Value="Not Started")});
     Collect(colDash_StatusTrack,{Status:"In Progress",Count:CountIf(Lead_Data,Status.Value="In Progress")});
     Collect(colDash_StatusTrack,{Status:"Dead Deal",Count:CountIf(Lead_Data,Status.Value="Dead Deal")});
     Collect(colDash_StatusTrack,{Status:"Completed Deal",Count:CountIf(Lead_Data,Status.Value="Completed Deal")}),
     
     ClearCollect(colDash_StatusTrack,{Status:"Not Started",Count:CountIf(Lead_Data,Status.Value="Not Started"&&Or(AssignedTo.Value=varUser,LeadMember1.Value=varUser,LeadMember2.Value=varUser,LeadMember3.Value=varUser))});
     Collect(colDash_StatusTrack,{Status:"In Progress",Count:CountIf(Lead_Data,Status.Value="In Progress"&&Or(AssignedTo.Value=varUser,LeadMember1.Value=varUser,LeadMember2.Value=varUser,LeadMember3.Value=varUser))});
     Collect(colDash_StatusTrack,{Status:"Dead Deal",Count:CountIf(Lead_Data,Status.Value="Dead Deal"&&Or(AssignedTo.Value=varUser,LeadMember1.Value=varUser,LeadMember2.Value=varUser,LeadMember3.Value=varUser))});
     Collect(colDash_StatusTrack,{Status:"Completed Deal",Count:CountIf(Lead_Data,Status.Value="Completed Deal"&&Or(AssignedTo.Value=varUser,LeadMember1.Value=varUser,LeadMember2.Value=varUser,LeadMember3.Value=varUser))})
)


My gallery Items property is set to filter my SharePoint list data based on the user logged in. 

If(!IsEmpty(Filter(Admin_LeadData,FullName = varUser)), Lead_Data, 
Filter(Lead_Data, AssignedTo.Value = varUser || LeadMember1.Value = varUser || LeadMember2.Value = varUser || LeadMember3.Value = varUser || LeadMember4.Value = varUser))


What would be my best way to accomplish this without breaking the way my app is working aready? Is it possible? With this in mind I also want to next create a button on my red banner that will sort by the date created. 
Capture.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Further Drill Down Filtering

Hey @KimberlyM 

 

Please update the configs as:
 
Not Started Tab Button OnSelect property:
Set(.SelectedStatus ,"Not Started")
 
On my InProgress Tab Button OnSelect property:
Set(SelectedStatus, "In Progress")
 
On my Dead Deal Tab Button OnSelect property:
Set(SelectedStatus,"Dead Deal")
 
On my Completed Deal Tab Button OnSelect property:
Set(SelectedStatus,"Completed Deal")
 
On my Show All Tab Button OnSelect property:
Set(SelectedStatus,"All")
 
You can remove all the expressions from OnVisible property.
 
Sort Button: Set(SortByDate,!SortByDate)
 
Gallery Items: Sort(Filter(If(!IsEmpty(Filter(Admin_LeadData,FullName = varUser)), Lead_Data, Filter(Lead_Data, AssignedTo.Value = varUser || LeadMember1.Value = varUser || LeadMember2.Value = varUser || LeadMember3.Value = varUser || LeadMember4.Value = varUser)),If(SelectedStatus = "All", true, Status = SelectedStatus)),"createdon",If(SortByDate,Ascending,Descending))
 
Please remove all the other expressions.
 
Hope this Helps!
 
If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

View solution in original post

13 REPLIES 13
cwebb365
Level 10

Re: Further Drill Down Filtering

Just have a variable that is set called TabSelected and set it with on select of each on. You can use Set or update context. Set is easier to track IMo.

Then in your dataset filter just put all the existing comparisons in parentheses and then put a && (statusColumn = TabSelected)

So something like
If(!IsEmpty(Filter(Admin_LeadData,FullName = varUser)), Lead_Data,
Filter(Lead_Data, (AssignedTo.Value = varUser || LeadMember1.Value = varUser || LeadMember2.Value = varUser || LeadMember3.Value = varUser || LeadMember4.Value = varUser)&&(StatusTrack = TabSelected)))
KimberlyM
Level: Powered On

Re: Further Drill Down Filtering

@cwebb365  Thank you! I almost understand this (I am coming off of 3 day stomach flu and not feeling top game yet, couldn't remember my  computer password this morning either...). 

&& (statusColumn = TabSelected)  what should statusColumn be referencing? I have tried to reference my column name (Lead_Data.Status as well as Lead_Data.Status.Value) and my gallery display label (Dash_Status as well as Dash_Status.Text)

On each of my buttons I have put this on my onSelect property: 
Set(TabSelected, Dash_Status.Text = "Not Started")
Set(TabSelected, Dash_Status.Text = "In Progress")
Set(TabSelected, Dash_Status.Text = "Dead Deal")
Set(TabSelected, Dash_Status.Text = "Completed Deal")
Nothing on my Display All button as I haven't figured out how to show this yet.

Super User
Super User

Re: Further Drill Down Filtering

Hi @KimberlyM 

 

If you wish to sort the data based on created date, then you can set the following config as:
 
Screen -> OnVisible -> Set(SortByDate,true)
 
Sort Button -> OnSelect -> Set(SortByDate,!SortByDate)   ->>>>> this will toggle the true and false value for sorting it.
 
Gallery Items -> Sort(If(!IsEmpty(Filter(Admin_LeadData,FullName = varUser)), Lead_Data, Filter(Lead_Data, AssignedTo.Value = varUser || LeadMember1.Value = varUser || LeadMember2.Value = varUser || LeadMember3.Value = varUser || LeadMember4.Value = varUser)),"createdon",If(SortByDate,Ascending,Descending))
 
Here, createdon is the name of column in the datasource.
 
Also, if you wish to add the functionality of sorting this based on selected tab, the expression can be modified to:
Set(TabSelected, SelectedStatus = "Not Started")
Set(TabSelected, SelectedStatus = "In Progress")
Set(TabSelected, SelectedStatus = "Dead Deal")
Set(TabSelected, SelectedStatus = "Completed Deal")
Set(TabSelected, SelectedStatus = "All")
 
Dash_Status.Text , this is not the correct way to set the property of any control, you have to write the expression on the property itself.
 
The below expression can be used to show selected tab and the sort by date feature.
Gallery -> Items  -> 
Sort(Filter(If(!IsEmpty(Filter(Admin_LeadData,FullName = varUser)), Lead_Data, Filter(Lead_Data, AssignedTo.Value = varUser || LeadMember1.Value = varUser || LeadMember2.Value = varUser || LeadMember3.Value = varUser || LeadMember4.Value = varUser)),If(SelectedStatus = "All",true,statuscol = SelectedStatus)),"createdon",If(SortByDate,Ascending,Descending))
 
Here, statuscol is the name of column in the datasource.
 
Hope this Helps!
 
If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!
KimberlyM
Level: Powered On

Re: Further Drill Down Filtering

@yashag2255  Am I defining SelectedStatus somewhere? My brain is just not firing today. I am not understanding what I am missing.

cwebb365
Level 10

Re: Further Drill Down Filtering

No just having the Set command anywhere will create the variable. As for sort, you can add Sort() around all your filters for each gallery and plug in another variable that you set on screenload, and have the button on the top bar set that sort ascending / decending. 
KimberlyM
Level: Powered On

Re: Further Drill Down Filtering

Here is what I am seeing (screencap below) - SelectedStatus is unhappy. It keeps saying that it is not recognized. This is set on my gallery's Items property and on each of my tab buttons I have Set(TabSelected, SelectedStatus = "Not Started") respectively (tab button In Progress will be set to "In Progress", etc.) set on the OnSelect property. 

 

snippit.JPG

 

Thank you so much for the time and help on this. I am so close but so lost at the same time right now! 

cwebb365
Level 10

Re: Further Drill Down Filtering

Can't really decipher that, but you aren't using your variable as the status here, you are trying to test that it = "All". You should just have in the filter an && "Whateveryoustatuscolumnis" = SelectedStatus

This way you get a dynamic return to your gallery. You can put all your users in ( ) with the or's then && and another () with the status filter to get your and / or's to work right.
KimberlyM
Level: Powered On

Re: Further Drill Down Filtering

So if I go with this:

If(!IsEmpty(Filter(Admin_LeadData,FullName = varUser)), Lead_Data,
Filter(Lead_Data, (AssignedTo.Value = varUser || LeadMember1.Value = varUser || LeadMember2.Value = varUser || LeadMember3.Value = varUser || LeadMember4.Value = varUser) 
&& (Status = TabSelected))
)

The = has the error "Invalid argument type." So my column is called Status in my SharePoint list and if I add the Lead_Data.Status (my sharepoint list name), or a collection I have  being called as colDash_StatusFilter.Status I get delegation warnings.  I 90% understand what I am doing but I get confused on what to call where yet. Hence my trying of various logical test equations.

cwebb365
Level 10

Re: Further Drill Down Filtering

what column type is Status? If it's choice it should be Status.Value = TabSelected also, you need to make sure to define TabSelected with a Set(TabSelected,"Default") in your onVisable of that screen. "Default" being whatever tab you want to be showing by default.

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

Top Kudoed Authors
Users Online
Currently online: 82 members 3,296 guests
Please welcome our newest community members: