The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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
Solved! Go to 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
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] ) )
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
@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 ,
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.