cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
enlitzinger
Helper II
Helper II

Distinct function with gallery filter formula

I have a PowerBIintegration app - Gallery 1 represents that dataset.  I am trying to filter Gallery1 based on text values within a text input box - which is concatenated text values (pulled from a formviewer (or the specific datacardvalue)).  

 

I'd like Gallery 1 to be filtered based on the Lines of Business (Line(s) of xx00) value from my PowerBI Integration dataset.  In this example (attached), that is the value from my FormViewer1, that was concatenated into my TextInput2 box (The Edge)...its concatenated as there can be multiple Lines of Businesses all separated by a comma.  Taking it a step further, the field I'd like to display in Gallery 1 is the Core Data Source that my PowerBIIntegration table is looking at (in summary, I had to append a few tables in Power BI and my identifier is a column I created that represents the original data source (pic1 for reference)...incase you're wondering why I did it that way)).  

 

I can get the distinct formula to work fine, but when I try to add in a filter function is where I'm messing up.  I thought using a CONTAINS function would be the best to do as I will be filtering the gallery (which the powerbi dataset contains 1 value in each cell) by the 1 or many concatenated text values within the TextInput2 box.  Where did I go wrong?  Any help would be appreciated...thanks!!

 

Gallery1 Items formula to return all values (pic1):

PowerBIIntegration.Data.'Core Data Source'

 

Distinct formula working just fine when not filtering (pic2):

Distinct(PowerBIIntegration.Data,'Core Data Source')

 

Distinct formula not working when I try to add the desired filtering based on TextInput2 text values (pic3):

Distinct(Filter(PowerBIIntegration.Data.'Line(s) of_x00', Contains, TextInput2.Text),'Core Data Source').Result

1 ACCEPTED SOLUTION

Accepted Solutions
v-yutliu-msft
Community Support
Community Support

Hi @enlitzinger ,

Do you mean that textinput2 includes multiple value of Lines of Business and are separated by a comma?

Do you want your items includes one or more Lines of Business of textinput2?

Firstly, let me explain where's your problem.

The syntax of Filter should be like:

Filter(table, filter rule)

The filter rule is a formula to evaluate every record.

It should be like:

value in fieldname, value= fieldname,....

Here's a doc about Filter function for your reference:
https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-filter-lookup

 

 

Secondly, try this formula:

Distinct(
    Filter(PowerBIIntegration.Data,
           'Line(s) of_x00' in Split(TextInput2.Text,",").Result
            ),
         'Core Data Source'
         )

//use Split function to split data in textinput2 to several Lines of Business value, it will return a table.

Use "in" to compare a value and a table.

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-yutliu-msft
Community Support
Community Support

Hi @enlitzinger ,

Do you mean that textinput2 includes multiple value of Lines of Business and are separated by a comma?

Do you want your items includes one or more Lines of Business of textinput2?

Firstly, let me explain where's your problem.

The syntax of Filter should be like:

Filter(table, filter rule)

The filter rule is a formula to evaluate every record.

It should be like:

value in fieldname, value= fieldname,....

Here's a doc about Filter function for your reference:
https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-filter-lookup

 

 

Secondly, try this formula:

Distinct(
    Filter(PowerBIIntegration.Data,
           'Line(s) of_x00' in Split(TextInput2.Text,",").Result
            ),
         'Core Data Source'
         )

//use Split function to split data in textinput2 to several Lines of Business value, it will return a table.

Use "in" to compare a value and a table.

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (3,052)