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

[Anonymous]

Hi, community

Need help with next issue.

I have 2 tables

Table1 contains user list

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

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

Task	Users
100	1,3
101	4,5,3
102	3,5
103	2,6,4
104	3,5,1,6,4
105	1
106	2,4
107	6,3
108	4,2,3
109	6

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

Id	Title	Task count
1	User1	3
2	User2	3
3	User3	6
4	User4	5
5	User5	3
6	User6	4

Lookupvalue result with blank

[Zubair_Muhammad · Accepted solution]

@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]
        )
    )

[Anonymous]

@Zubair_Muhammad  Thanks a lot. Very hepful!

[Mariusz]

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.

[Anonymous]

@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?

[Mariusz]

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.

[Anonymous]

@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.

[Mariusz]

Hi @Anonymous 

Are you concatenating user column?

Best Regards,
Mariusz

Please feel free to connect with me.