Forum Discussion
Table Connection Problem
Hello
I have two tables in PowerBI Desktop report
Table-1: Having 3 columns - S.No., Team Member and Tasks done
Table-2: Having 1 column - Task Name
All the tasks done by each member are shown in single column in Table-1, the tasks can be in random order (as shown in the snapshot)
In Model view I have connected Tasks done (Table-1) and Task Name (Table-2)
Required Output: When a task is selected in Table-2 then all the rows in Table-1 having that task should be visible.
Problem Statement:
- If there is no connection done in Model view then Table-1 is not affected by any selection in Table-2
- If the connection is made between Tasks done (Table-1) and Task Name (Table-2), then Table-1 becomes blank as soon as any task is selected in Table-2
Please help to resolve the issue.
Snapshot-1: Report View
Snapshot-2: Model View
Best Regards
Arvi-Rooprai
Hi arvi-rooprai ,
I made simple samples and you can check the results below:
Find = var _select = SELECTEDVALUE('Table (2)'[Task Names]) var _find = CONTAINSSTRINGEXACT(MAX('Table'[Task Done]),_select) RETURN IF(_find,1,0)An attachment for your reference. Hope it helps!
Best regards,
Community Support Team_ Scott ChangIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
- Hi v-tianyich-msft
Thanks for the prompt reply.Your solution looks great and seems to solve the problem, I applied your solution and "find" measure is working, but rows in the table are not getting affected (I mean the find function shows the 0 and 1 for the selected values, but rows with 0 are also visible) - I have removed the connection in Models but still all rows are visible at all the time.Please see the snapshot:Best RegardsArv-Rooprai
9 Replies
The issue goes beyond to the way your data is originally structured. Tasks done are separated by commas in a single row. This will restrict you in data granularity and the possibility to create and adequate data relationship. You should aim for a table structure such as:
S.No Member Task Done 1 Member 1 Task-1 1 Member 1 Task-3 1 Member 1 Task-5 2 Member 2 Task-2 This will allow you to create a 1 to many relationship between Task Names and Task Done. To achieve this, most likely you will need to split the column values and then unpivot those columns.
Hello ray_aramburo
Thank you for the prompt reply.
The data in column "Task done" has to be in the same way as I need to see the multiple tasks done by each member, not only the task number but the task count is not constant. So the solution has to work on this database only. However I hav tried creating the helping columns by seperating each task in different column, but it could not help either.
Thank you so much for your response. I will update here for whichever solution will work.Best Regards
Arvi-Rooprai
Hi arvi-rooprai ,
I made simple samples and you can check the results below:
Find = var _select = SELECTEDVALUE('Table (2)'[Task Names]) var _find = CONTAINSSTRINGEXACT(MAX('Table'[Task Done]),_select) RETURN IF(_find,1,0)An attachment for your reference. Hope it helps!
Best regards,
Community Support Team_ Scott ChangIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
- Hi v-tianyich-msft
Thanks for the prompt reply.Your solution looks great and seems to solve the problem, I applied your solution and "find" measure is working, but rows in the table are not getting affected (I mean the find function shows the 0 and 1 for the selected values, but rows with 0 are also visible) - I have removed the connection in Models but still all rows are visible at all the time.Please see the snapshot:Best RegardsArv-RoopraiHi arvi-rooprai ,
Please find Find in the filter and set the value to "is 1".
Hope it helps!
Best regards,
Community Support Team_ Scott ChangIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
In the Query Editor, split the task done column in Table1 by , (comma and space) and in that window itself, select Rows (in Advanced). Now you should be able to build your visual.
Hi Ashish
Thanks for the prompt reply.
Can you share more details of what you have suggested, perhaps with screenshots to help.
Best Regards
Arvi-Rooprai
