Table Connection Problem

[arvi-rooprai]

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

[v-tianyich-msft · Accepted solution]

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 Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

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.

[arvi-rooprai]

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

[v-tianyich-msft · Accepted solution]

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 Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[arvi-rooprai · Accepted solution]

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 Regards

Arv-Rooprai

[v-tianyich-msft]

Hi @arvi-rooprai ,

Please find Find in the filter and set the value to "is 1".

 Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[arvi-rooprai]

Hi v-tianyich-msft

Thank you so much - it finally worked. 😊

One more help - in continuation of this if I may ask?

In my actual table, I have some additional visuals in my report like "Card" that displays the count of selected members in Table-1.

Ideally when we select a task name in Table-2, the data in Table-1 gets updated and the count of members (displayed in Table-1) should also be updated - but this is not happening right now.

I am sharing another snapshot. Though this is a minor requirement, but if it get's resolved then it would be very helpful.

Best Regards

Arvi-Rooprai

[v-tianyich-msft]

Hi @arvi-rooprai ,

Sorry for late reply,You can create another measure:

Measure = CALCULATE(COUNT('Table'[No]),FILTER('Table',[Find]=1))

Best regards,
Community Support Team_ Scott Chang

[ray_aramburo]

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. 

[arvi-rooprai]

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