recursive calculation

[faheem_Latif_BI]

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.

[123abc · Accepted solution]

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.