Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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
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
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 =
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.
User | Count |
---|---|
21 | |
14 | |
11 | |
8 | |
5 |
User | Count |
---|---|
24 | |
22 | |
20 | |
15 | |
10 |