Complex Filter(Two list), grouping by date and graph

[Charlyvil]

Hi everyone!

I've been thinking about a solution for two days and I haven't been able to solve it. I hope you can help me:

I have two tables like this:

stores

store	Section	ID	Date
Blue	Section 1	1	10/17/2019
Yellow	Section 1	2	10/17/2019
Blue	Section 2	3	11/17/2019
Blue	Section 1	4	10/17/2019
Pink	Section 1	5	12/12/2019

relationSells

IDProduct	productType	IDStore
1	1	1
2	1	1
3	3	1
1	1	4
2	2	5
2	2	4

In PowerApps y have two dropdown list:

dropDown1.Items = Distinct(Stores.store)
dropDown2.Items = I have a Filter to show available sections for that store

So I need to filter the data(stores):

store	Section	ID	Date
Blue	Section 1	1	10/17/2019
Blue	Section 1	4	10/17/2019

Next I need to filter all ID’s (1 and 4) in relationSells:

IDProduct	productType	IDStore
1	1	1
2	1	1
3	3	1
2	2	4

After counting the 1,2,3,etc from productType:

productType	Count
1	2
2	1
3	1

And finally graph this data per month to report annually. (The date is getting from stores list):

If you have a suggestion or solution I appreciate very much.

Thank you

[v-yutliu-msft · Accepted solution]

Hi @Charlyvil ,

I've made a similar test based on your description.

Here's my test for your reference:

1)

dropDown1.Items = Distinct(Stores.store)
dropDown2.Items = I have a Filter to show available sections for that store 

 set drop down1's Items:

Distinct(stores,store)

 set drop down2's Items:

Distinct(Filter(stores,store=Dropdown1.Selected.Result),Section)

2)filter the data(stores):

set the data table's Items:

Filter(stores,store=Dropdown1.Selected.Result,Section=Dropdown2.Selected.Result)

3) filter all ID’s (1 and 4) in relationSells:

set the data table's Items:

Filter(relationSells,IDStore in Filter(stores,store=Dropdown1.Selected.Result,Section=Dropdown2.Selected.Result).ID)

4) coun the 1,2,3,etc from productType:

set the data table's Items:

AddColumns(GroupBy(Filter(relationSells,IDStore in Filter(stores,store=Dropdown1.Selected.Result,Section=Dropdown2.Selected.Result).ID),
                   "productType","sametype"),
           "Count",CountRows(sametype))

5) graph this to column chart 

set the columnchart's Items:

AddColumns(GroupBy(Filter(relationSells,IDStore in Filter(stores,store=Dropdown1.Selected.Result,Section=Dropdown2.Selected.Result).ID),
                   "productType","sametype"),
            "Count",CountRows(sametype))

However, your last demands "graph this data per month to report annually", I think this step need use flow. In PowerApps, it will not generate graph automatically per month.

Best regards,

[Charlyvil]

Thank you so much 

You win pass to heaven.