Creating a filtered table via a contains function

[cartman21]

Hello,

I have an urgent question for which I could really use some help.

Basically, I have a huge amount of data pulling in from an external resource, which is great.

I have say, 3000 companies and for them, investor data.

Company   Investor

Apple             Rafa           

Microsoft       Bill

Acer               John

Tito                Rafa, Niko

Logitech         Dino

Then, I have a seperate list of 'key investors. Lets say this list is

1) Rafa

2) Bill

How do I get to a table that shows me the companies filtered by the investors in this list of 2 priority people as above?

Company     Investor

Apple             Rafa

Microsoft       Bill

Tito                Rafa, Niko

I think it has something to do with the contains function,...but really don't get this at all. Any help would be much appreciated - on a strict deadline for this task. 

[MFelix]

Hi @cartman21,

I'm assuming the key investor list is in a separate table, if so just do a relationship between the two table and use the investor key table in your visual to have the companies per user and filter the blanks out of the key investor column.

Regards,

MFelix

[cartman21]

Hello, thanks for taking the time to respond!

The problem is that the list is of KEY investors is like this:

But the data i am being fed has stuff like this:

So the method you provide sadly does not work due to this reason.

Any ideas?

[v-huizhn-msft]

Hi @cartman21,

If the investors name are in fixed location in your resource table.  In Query Edit navigator, you split the column into separate columns by delimiter(blank), get the investors column like the example you given above, then you can the solution posted by @MFelix.

In addition, from the screenshot below, the column is the investors or company fields? Could you please give more details, so that we can post the solution which is close to what you want.



Best Regards,
Angelia

[cartman21]

Sorry I think my description was not good enough.

So basically, I have an API which I have created a report with in Power BI.

Here is the table I have working:

If i use a delimiter, I get in row 4 for example different columns such as:

My issue is i need to be able to search in the report (not in the query/backend) for say, 360 capital. 

I have tried the slicer, but then I have to select each iteration of 360 capital that shows up. Is there a way I can just type in a string into a slicer/search and all companies with investors matching the string show up in the table?

Here is the problem with the slicer----

and i can't find a 'select all' feature.

Any help with this would be greatly appreciated!!

[MFelix]

Hi @cartman21,

Not abble to see this in the afternoon will try to get back to you tomorrow morning.

regards

MFelix

[cartman21]

hi @MFelix any help would be much appreciated, thanks!

[Vvelarde]

@cartman21

Hi, a partial solution.  

This works only with 1 selection in slicer of investors.

A measure:

MatchInvestor =
IF (
    HASONEVALUE ( Investors[Investor] ) && HASONEVALUE ( Table1[Investor] ),
    IF (
        FIND (
            SELECTEDVALUE ( Investors[Investor] ),
            VALUES ( Table1[Investor] ),
            1,
            0
        )
            <> 0,
        1,
        0
    )
)

And add in the Visual level filter of your table visual and select only when measure is 1.

Regards

Victor

Lima - Peru

[cartman21]

Anyone?