Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Anonymous
Not applicable

Search and count rows in one table with contains Id from another

Hi, community

Need help with next issue.

I have 2 tables

 

Table1 contains user list

IdTitle
1User1
2User2
3User3
4User4
5User5
6User6

Table2 contains task info: Task№ and Id of users, which work with each task.

TaskUsers
1001,3
1014,5,3
1023,5
1032,6,4
1043,5,1,6,4
1051
1062,4
1076,3
1084,2,3
1096

 

I need count tasks for every user in table1. Something like that

IdTitleTask count
1User13
2User23
3User36
4User45
5User53
6User64

 

Lookupvalue result with blank

1 ACCEPTED SOLUTION
Zubair_Muhammad
Community Champion
Community Champion

@Anonymous 

 

Here is a way of doing this in DAX using calculated column.

See file attached as well

 

Calc Column =
VAR myvalues =
    CONCATENATEX (
        SELECTCOLUMNS ( Table2, "My_IDs", SUBSTITUTE ( [Users], ",", "|" ) ),
        [My_IDs],
        "|"
    )
RETURN
    COUNTROWS (
        FILTER (
            ADDCOLUMNS (
                GENERATESERIES ( 1, PATHLENGTH ( myvalues ) ),
                "MyUsers", PATHITEM ( myvalues, [Value], INTEGER )
            ),
            [MyUsers] = Table1[Id]
        )
    )

GS.png

View solution in original post

7 REPLIES 7
Zubair_Muhammad
Community Champion
Community Champion

@Anonymous 

 

Here is a way of doing this in DAX using calculated column.

See file attached as well

 

Calc Column =
VAR myvalues =
    CONCATENATEX (
        SELECTCOLUMNS ( Table2, "My_IDs", SUBSTITUTE ( [Users], ",", "|" ) ),
        [My_IDs],
        "|"
    )
RETURN
    COUNTROWS (
        FILTER (
            ADDCOLUMNS (
                GENERATESERIES ( 1, PATHLENGTH ( myvalues ) ),
                "MyUsers", PATHITEM ( myvalues, [Value], INTEGER )
            ),
            [MyUsers] = Table1[Id]
        )
    )

GS.png

Anonymous
Not applicable

@Zubair_Muhammad  Thanks a lot. Very hepful!

Mariusz
Community Champion
Community Champion

Hi @Anonymous 

 

Please find the attached file with the solution, all you need to do is split your Users Column into rows in Table2 and later join this column on Id from Table1

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski



 

 

Anonymous
Not applicable

@Mariusz , thank you for quick answer.

 

But, the problem is that Table2 is created in report , so I have no Table2 in queries, so I can't use split by delimiter in query.

Is there any other method to split colunms into rows?

Hi @Anonymous 

 

Is it created as a daxDAX expression?

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski

 

Anonymous
Not applicable

@Mariusz ,

Yes

In query I have two columns in Table2 with Id lists (resposible users and informed users), I splited them and extract values, then concatinate two columns to get whole number of paticipants.

Hi @Anonymous 

 

Are you concatenating user column?

 

Best Regards,
Mariusz

Please feel free to connect with me.
Mariusz Repczynski

 

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors