Show sub data from parent data from the same data source

[ja_02]

Hi,

I currently have 2 tables which relation can be shown as below. The tasks table would have 2 type of task :
- Main task -> ID will be whole number (1,2,3,etc)
- Sub task -> combination of Main task ID & sub task ID (1.1, 1.2, 2.1, 2.2, etc) which means 1.1 is a sub task of main task with ID 1.

The Assignee can contains > 1 name with comma separated : Name1, Name2, Name3 and TaskAssignee is a mapping from task ID to each of its assignee to achieve a better visualization (I currently have bar visualization which count task for each assignee individually and could be achieved using this TaskAssignee mapping table)
 

The problem is, I have 2 table visuals, #1 is for Main task and #2 is for Sub task (I add TaskType calculated column to the visual filter so #1 would have filter TaskType=Main and vice versa). What I want is: when I click on a main task, then the table #2 would how only sub-tasks related to the specific ID for that selected main task, otherwise show all sub tasks. 

Current situation: If i click on a main task, the #2 would be empty, I guess there is clashing filter. I tried duplicating the tasks table, but the problem will rely on the TaskAssignee table since it only retrieves data from the master table. Is there any idea how to achieve the solution?

[v-sgandrathi · Accepted solution]

Hi @ja_02,

Thank you for your question. Based on the scenario you described, where you are working with a Tasks table containing both main tasks and sub-tasks, and you would like the second visual (sub-tasks) to dynamically respond to selections made in the first visual (main tasks), please find below our recommended approach:

In your model, main tasks use whole numbers (e.g., 1, 2) and sub-tasks use decimals (e.g., 1.1, 1.2), indicating a parent-child structure. Since this isn't explicitly defined, create a calculated column in the Tasks table (e.g., MainTaskID) to extract the parent ID for each sub-task.

To avoid filtering conflicts between two visuals from the same table, create a disconnected table containing only main task IDs. Use this table in the main task visual.

Then, define a DAX measure that:

• Returns 1 when a sub-task's MainTaskID matches the selected main task
• Returns 1 when no selection is made (to show all sub-tasks)
• Returns 0 otherwise

Apply this measure as a visual-level filter in the sub-task table.

Your TaskAssignee mapping will still work, since we’re not changing relationships just controlling visuals with DAX.

This setup lets users click a main task and automatically see its related sub-tasks, or see all sub-tasks when nothing is selected achieving both clarity and flexibility.

If this solution worked for you, kindly mark it as Accept as Solution and feel free to give a Kudos, it would be much appreciated!

Kind regards,
Sahasra
Customer Support Team.

[v-sgandrathi]

HI @ja_02,

We haven’t heard from you on the last response and was just checking back to see if your query was answered.
Otherwise, will respond back with the more details and we will try to help .

If our response has addressed your query, please accept it as a solution and give a ‘Kudos’ so other members can easily find it. Please let us know if there’s anything else we can do to help.

Thank you.

[v-sgandrathi]

Hi @ja_02,

I wanted to follow up on our previous suggestions regarding the issue. We would love to hear back from you to ensure we can assist you further.

If our response has addressed your query, please accept it as a solution and give a ‘Kudos’ so other members can easily find it. Please let us know if there’s anything else we can do to help.

Thank you.

[v-sgandrathi]

Hi @ja_02,

Hi ,
we haven't heard back from you regarding our last response and wanted to check if your issue has been resolved.

If our response addressed by the community member for  your query, please mark it as Accept Answer and give us Kudos. Should you have any further questions, feel free to reach out.

Thank you for being a part of the Microsoft Fabric Community Forum!

[burakkaragoz]

Hi @ja_02 ,

You can totally build that kind of table in Power BI, even if Sales#1 and Sales#2 are in separate tables with no shared dimension.

Here’s a simple way to do it:

1. Create a Date table and use it as the common axis for both tables.
2. Use TREATAS in your measures to align both sales tables to the Date table:

   Sales1 = CALCULATE(SUM(Sales1[Amount]), TREATAS(VALUES('Date'[Date]), Sales1[Date]))
   Sales2 = CALCULATE(SUM(Sales2[Amount]), TREATAS(VALUES('Date'[Date]), Sales2[Date]))
   Difference = [Sales1] - [Sales2]

1. Add these measures to a matrix visual with Month/Year from the Date table as rows.

For the total-only table, just use a card or table visual with:

Total = [Sales1] + [Sales2]

This setup works even without direct relationships between the tables.

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.