Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Not applicable

Find the value based on Comma seperated list

I have a Table - 1 with a path (comma seperated value) of everyone who reports to someone

Table 1 Column1 - value A,B,C

example : A,B,C where A and B reports to C


Another Table , Table 2 which  has a value of C

ColumnName  = Level Value C

ColumnName = Person

ColumnName = Amount


I need Table 2 to to look into table 1 and get the values of both A and B and then display them

Also , the "Amount" column from table 2 where Table1Column1 = Table2Columnname Person



Community Support
Community Support

Hi @Anonymous ,


We can create this column in tables to show who to report.


Report man = 
    IF (
        FIND ( [Level], [Column1], 1, -1 )
            = (
                LEN ( [Column1] ) - LEN ( [Level] ) + 1



Then we can create such column to show the sub people.


Sub People =
VAR t =
    FILTER ( 'Table', [Level] = [Report man] )
VAR result =
    MAXX (
        REPLACE (
            LEN ( [Column1] ) - LEN ( [Level] ),
            LEN ( [Level] ) + 1,
    SUBSTITUTE ( result, ",", " & " )



But I connt get the point of the meaning of “Amount”.


If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.


BTW, pbix as attached.


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

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

Hi @Anonymous 

For table1 , you could split the delimitted collumn into more/other columns.

Go to edit query >> Right click the column >> Split Column (By Delimiter)

This will create a table with 3 columns. A,B,C --> A | B | C

Create another custom column to concatenate all values that are not reported to.

A | B | C --> A | B | C | A, B



2019-08-30 11_00_03-Window.png


2019-08-30 11_00_27-Window.png

In table2 you can create a look up to match the reporting to values and retreive the concatenated column.


Not sure what you need with the amount.



Not applicable

That might not work in my case.

Is there a DAX I could use to find/Search the column .. Since the value in column 1 will always be as one of the values  in the comma seperated value.

Can you give an exemple of what you want to search?

This how you search for a comma. You can use it in a context like LEFT([sales person],FIND(...)-1) kind of thing.


Not applicable

Hey @Anonymous 

As I understand, the value to search will always be in the last column.

A,B,C so you want to find 'C'
C,D so you want to find 'D'

D so there is no one to find, as D is not reporting to anyone.


Bottom line,

The way the values are ordered, is the key to finding the values later on.



I believe you need to create a bridging_table to link table A to B.


 It will involve the split solution proposed by @Anonymous . but would be better off creating it in a referenced table and pivoting the result to link to use it as a link.


Something like this:









Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors