Company name to reflect based on drop down

[nagaraj007]

Hello All, 

I have groupby formula which is used inside gallery. I have 2 year drop down where if the user selects 2008 year from one drop down and 2010 from other dropdown for comparison of 2 years data. 

Right now in group by function I have added dropdown1 result, so the result will show company which has data based on that, if I change from dropdown1 to 2 also it shows only for that. 

I want gallery to show company names against those selected dropdowns, even if the data is available in any one of the year. Please advise how to do this. 

@eka24

[eka24]

Please show a screenshot of the formulas in the Dropdowns.

Also what formula do you have in the Gallery items

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[nagaraj007]

Hi,

Gallery formula is below

Dropdown (From year) formula is

Dropdown (To year) formula is

[nagaraj007]

Hi Eka,

I used the formula you gave to me

SortByColumns(GroupBy(Filter(MyCombinedTable,Year = 'from year'.Selected.Result Or Year=

'to year'.Selected.Result),"Company Name","Membership No.","Membership Category","grpcomp"),"Company Name",Ascending)

But its showing all the Companies even if the data is not there for both the years. 

I want to filter and show Company list if the data is available in any of the one year 

[eka24]

Try

SortByColumns(GroupBy(Filter(MyCombinedTable,Year = 'from year'.Selected.Result && Year=

'to year'.Selected.Result),"Company Name","Membership No.","Membership Category","grpcomp"),"Company Name",Ascending)

Do you have a separate Column in your table for From And To Dates or only one column for all the dates.

If you have separate columns use

SortByColumns(GroupBy(Filter(MyCombinedTable,FromColumn = 'from year'.Selected.Result Or ToColumn=

'to year'.Selected.Result),"Company Name","Membership No.","Membership Category","grpcomp"),"Company Name",Ascending)

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[nagaraj007]

Hi,

I have only one column called Year which i will be using for both the dropdown for comparing.

I used the first formula but the gallery is showing blank. Please advise.

[v-yutliu-msft]

Hi @nagaraj007 ,

Do you want to display items based on year in drop down1 or year in drop down2?

Could you tell me the data type of the Year field?
If yes, you should use || to connect two conditions.

SortByColumns(
 GroupBy(
      Filter(MyCombinedTable,Year = 'from year'.Selected.Result || 
                             Year='to year'.Selected.Result),
"Company Name","Membership No.","Membership Category","grpcomp"
         ),"Company Name",Ascending)

Actually, I do not think there's any problem in this part:

Year = 'from year'.Selected.Result ||
Year='to year'.Selected.Result

The key point should be about data type.

So please show me more details.

Best regards,

[nagaraj007]

Hi,

The Output i am looking is if the user selects 1994 vs 1998, i want the data to be shown against Company if data is there in any of the year. 

For ex:

In 1994 A has data of 240 and in 1998 A doesnt have any data, this should be shown. 

In the similar way

In 1994 A doesnt have any data and in 1998 A has the data of 289, this should be shown. 

So I want both the data to be shown.

[v-yutliu-msft · Accepted solution]

Hi @nagaraj007 ,

Do you mean that you want to filter year within the two selected years?

If so, try this formula:

SortByColumns(
 GroupBy(
      Filter(MyCombinedTable,Year>='from year'.Selected.Result,
                             Year<='to year'.Selected.Result),
"Company Name","Membership No.","Membership Category","grpcomp"
         ),"Company Name",Ascending)

Best regards,