Gallery filter - powerapps

Question

I have table with 60 columns for e.g the table looks like below

Name  Process1         Process2 ..............  Process59
A            1             2                         0
B            4             0                         0
C            2             3                         1

In the powerapp - i have already got the name from the user. in our case if the user picks "A" then i want my gallery to only show the process columns where A has value greate than 0

in this case if "A" is selected then the gallery will only display Process 1, process 2 and its value

if the user selects "B" then only Process 1 will get displayed

How do i go about achieving it - i know i have to play with filter - but how do i loop through all columns in the table to find value greater than 0 for the selected user?

score 1 · Accepted Answer · edited Aug 15 '20 at 19:58

You cannot have an expression that directly filters and gives the column names as results... What you can do is unpivot your table, so that you have a structure like the one below:

Name  Process    Value
A     Process1   1
A     Process2   2
...
A     Process59  0
B     Process1   4
B     Process2   0
...
B     Process59  0
C     Process1   2
C     Process2   3
...
C     Process59  1

Once you have your data in that format, you can apply the filter based on the column values.

To create the unpivoted table, you can use an expression like this one (assuuming that your data source is called 'MyDataSource'):

    Clear(UnpivotedCollection);
    ForAll(
            MyDataSource,
            Collect(
                    UnpivotedCollection,
                    { Name: Name, Attribute: "Process1", Value: Process1 },
                    { Name: Name, Attribute: "Process2", Value: Process2 },
                    { Name: Name, Attribute: "Process3", Value: Process3 },
                    ...
                    { Name: Name, Attribute: "Process58", Value: Process58 },
                    { Name: Name, Attribute: "Process59", Value: Process59 }))

If you have this expression in the App's OnStart property (or in some other place where you can refresh the unpivoted collection after updates to the data source), you can start using it. Now, if you have a Dropdown control with the following expression for the Items property:

MyDataSource.Name

You can then have a gallery with the following expression for its Items property:

Filter(
    UnpivotedCollection,
    Name = Dropdown1.Selected.Name
    Value > 0)

And you will have in the gallery the records for the given name with positive values.

Thank you so much carlos for such a detailed answer. Yes i was initially trying to avoid change the table format - but doing it makes it much more easier to handle this. — Neo, Aug 17 '20 at 17:47
one more question , I am inserting powerbi tile in the powerapp and if i pass a string URL for filter - it doesnt filter and it shows up the same report where as if i embed the report i can filter it. Any thoughts — Neo, Aug 17 '20 at 17:48
I'm not sure about this other issue with Power BI... You can try creating a new question for that, with detailed information about how you are embedding, passing the URL for filter, any relevant information that can help someone figure out the problem. — carlosfigueira, Aug 18 '20 at 14:42

Gallery filter - powerapps

1 Answers1