Based on your and others wonderful support I made the following,
I want the location chart to filter based on the Year selection in the dropdown. In the location summary the SalesYear column has under the group. I tried following way but did not work
Solved! Go to Solution.
Ok, right, now I see, this is because you have multiple rows in each Grouped.SalesYear, so it's a list of entries rather than one item, so we can't compare a box of Apples with an Apple (if that makes sense?)
Could you try these two different formulae? I'm not actually 100% sure these will work, as I think the issue is that it's not able to compare multiple sub-items to a singular parent item.
Filter(SalesSumbyLocation, Dropdown2_1.Selected.Value in Text(Grouped.SalesYear.Value, "YYYY"))
Filter(SalesSumbyLocation, Text(Grouped.SalesYear.Value, "YYYY") = Dropdown2_1.Selected.Value)
Another way of solving this would be to create a new collection, which has the parent (SalesBySumLocation) ID as a new column maybe called RowID, and the contents of Grouped.SalesYear, using AddColumns and ClearCollect, and then filtering That to then show only those items where the Year matches, then comparing your original to this filtered list where the ID's and RowID's match.
Thank you @PowerAddict I tried but did not work
My bad, yes the Left function was incorrect. Try this:
If(Dropdown2_1.Selected.Value="All",SalesSumbyLocation,Filter(SalesSumbyLocation, Left(Grouped.SalesYear, Find(Grouped.SalesYear,".")) = Text(Dropdown2_1.Selected.Value)))
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution. Thanks!
Read the announcement for more information!
Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.
Features releasing from October 2019 through March 2020
Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications