topic Re: Help with DAX Measures on Parent Child tables recursively in DAX Commands and Tips

Help with DAX Measures on Parent Child tables recursively

abhiram342 — Mon, 23 Oct 2023 02:29:15 GMT

Hi Team,

I have two tables, Pipelines and Activities. Pipeline Table has ParentPipelineId's, I want to create below measures by travelling recursively at the root level. I have created basic measurse but need help on recursive part. Please help

Measues:

# of Automate ==>

Example: For PipelineType ==> A => We want to travel till root (child) and consider count all the activities, in this case it should Activities For 123 (3) + Activities for 456 (2) + Activities for 789 (2)

# of Manual ==> Same Behaviour as Automate by TaskType will be Manual

# of Unique Activities => We want to travel till root and consider distinct Activity ID

Example: For Pipeline TypA, Activity ID 1 is repeating in both PipelineId 123 and 789, we want to count as 1.

Relationship:

Pipelines ( PipelineId - 1) <-------- Activities ( PipelineId --*)

Thanks,

Abhiram

Re: Help with DAX Measures on Parent Child tables recursively

123abc — Mon, 23 Oct 2023 06:13:09 GMT

To create DAX measures that count the number of Automate, Manual, and Unique Activities by recursively traversing the parent-child hierarchy in your Pipelines table, you can use DAX functions like SUMX, FILTER, and recursion with a custom DAX function. Here's a step-by-step guide on how to achieve this:

Step 1: Create a DAX Function for Recursive Calculation

First, you need to create a custom DAX function that can recursively traverse the parent-child hierarchy in your Pipelines table. You can do this by using a recursive CTE (Common Table Expression) in Power Query. Here's a simplified example of such a function:

RecursivePipelineActivities =
VAR Recurse =
ADDCOLUMNS (
Pipelines,
"ActivitiesCount",
COUNTROWS (
FILTER (
Activities,
RELATED ( Activities[PipelineId] ) = Pipelines[PipelineId]
)
)
)
VAR Result =
SUMX ( Recurse, [ActivitiesCount] )
RETURN
Result

This function, RecursivePipelineActivities, will calculate the sum of activities for the given pipeline and all its children.

Step 2: Create Measures

Now, you can create measures based on the custom function. Here's how you can create the measures you mentioned:

# of Automate:

# of Automate =
VAR TypeToCount = "A"
VAR PipelinesWithAutomate =
FILTER ( Pipelines, Pipelines[PipelineType] = TypeToCount )
RETURN
[RecursivePipelineActivities]

# of Manual:

# of Manual =
VAR TypeToCount = "M"
VAR PipelinesWithManual =
FILTER ( Pipelines, Pipelines[PipelineType] = TypeToCount )
RETURN
[RecursivePipelineActivities]

# of Unique Activities:

# of Unique Activities =
VAR DistinctActivities =
SUMMARIZE ( Activities, Activities[ActivityId] )
RETURN
COUNTROWS ( DistinctActivities )

These measures use the RecursivePipelineActivities function to calculate the counts of activities for the specified pipeline types and then count unique activities for the third measure.

Make sure you adjust the TypeToCount variable to match the specific types you want to count.

This approach assumes you have created relationships between the Pipelines and Activities tables as you described in your question. Also, please note that the provided DAX code is a simplified example, and you may need to adapt it to your specific data model and requirements.

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.

Re: Help with DAX Measures on Parent Child tables recursively

abhiram342 — Tue, 24 Oct 2023 01:28:12 GMT

Hi 123abc - Thank you for your response. I wwas facing issuing while creating function. Can you please tell what's input for function? I copied function code in measure but it's complainig about relationship, I have valid relationship between table. Also, For # of Automate, I don't want to harcode PipelineType, I assume It's TaskType = "Automate" . I tried to create measure in Activity table but it has same error.

Please find screenshot below

Thanks,

Abhiram