Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply

recursive calculation

Hi Gurus,

I just need to know can we create a DAX calculations using parent child recursive options,

 

here is a case.

Project > Idea >PBI > Task 

Now i need to calculates time of task but dax will start from project using parent shild relationship.

 

1 ACCEPTED SOLUTION
123abc
Community Champion
Community Champion

Yes, it is possible to create recursive calculations in DAX (Data Analysis Expressions) using parent-child relationships. In your case, you want to calculate the time of tasks starting from the project level using the parent-child hierarchy of Project > Idea > PBI > Task.

Here's a general approach you can follow:

  1. Define the Parent-Child Relationship: Ensure that your data model has a proper parent-child relationship established between Project, Idea, PBI, and Task entities.

  2. Create a DAX Measure for Recursive Calculation: You will need to create a DAX measure that recursively calculates the time of tasks starting from the project level.

  3. Utilize DAX Functions: DAX offers functions like PATH, PATHITEM, and PATHCONTAINS that can be helpful in dealing with parent-child hierarchies.

Here's a simplified example of how you might write a DAX measure for recursive calculation:

 

RecursiveTaskTime =
VAR CurrentProject = SELECTEDVALUE('Project'[ProjectName])
RETURN
IF (
ISFILTERED('Task'[TaskName]), -- Check if we are at the Task level
CALCULATE(
SUM('Task'[Time]), -- Calculate the time of the task
FILTER(
ALL('Task'),
'Task'[TaskName] = SELECTEDVALUE('Task'[TaskName]) -- Filter to the specific task
)
),
-- Otherwise, we need to go up the hierarchy
CALCULATE(
SUM('Task'[Time]), -- Calculate the time at the current level
FILTER(
ALL('Project'),
'Project'[ProjectName] = CurrentProject -- Filter to the current project
)
) + [RecursiveTaskTime] -- Add the time of child PBIs and Tasks recursively
)

 

Please note that this is a simplified example, and you may need to adapt it to fit your specific data model and requirements. Additionally, recursive calculations in DAX can be resource-intensive, so it's important to test the performance of your measures, especially with large datasets.

 
 
 
 

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

 

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

View solution in original post

1 REPLY 1
123abc
Community Champion
Community Champion

Yes, it is possible to create recursive calculations in DAX (Data Analysis Expressions) using parent-child relationships. In your case, you want to calculate the time of tasks starting from the project level using the parent-child hierarchy of Project > Idea > PBI > Task.

Here's a general approach you can follow:

  1. Define the Parent-Child Relationship: Ensure that your data model has a proper parent-child relationship established between Project, Idea, PBI, and Task entities.

  2. Create a DAX Measure for Recursive Calculation: You will need to create a DAX measure that recursively calculates the time of tasks starting from the project level.

  3. Utilize DAX Functions: DAX offers functions like PATH, PATHITEM, and PATHCONTAINS that can be helpful in dealing with parent-child hierarchies.

Here's a simplified example of how you might write a DAX measure for recursive calculation:

 

RecursiveTaskTime =
VAR CurrentProject = SELECTEDVALUE('Project'[ProjectName])
RETURN
IF (
ISFILTERED('Task'[TaskName]), -- Check if we are at the Task level
CALCULATE(
SUM('Task'[Time]), -- Calculate the time of the task
FILTER(
ALL('Task'),
'Task'[TaskName] = SELECTEDVALUE('Task'[TaskName]) -- Filter to the specific task
)
),
-- Otherwise, we need to go up the hierarchy
CALCULATE(
SUM('Task'[Time]), -- Calculate the time at the current level
FILTER(
ALL('Project'),
'Project'[ProjectName] = CurrentProject -- Filter to the current project
)
) + [RecursiveTaskTime] -- Add the time of child PBIs and Tasks recursively
)

 

Please note that this is a simplified example, and you may need to adapt it to fit your specific data model and requirements. Additionally, recursive calculations in DAX can be resource-intensive, so it's important to test the performance of your measures, especially with large datasets.

 
 
 
 

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

 

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors