Help with calculating the # of records in one table based on criteria from a second table

festazen — Wed, 28 Feb 2024 04:25:35 GMT

Not sure how to describe what I'm looking to do so I'll show you examples of the tables I have and the output I'm trying to achieve.

I have tow tables (Opportunities and Milestones) that are connected by the OpportunityID Column.

The Opportunities table records each have a unique ID (OpportunityID)

The Milestones table represents child records of the Opportunities table.

Each Opportunities record has one or more Milestones records.
Each Milestones record can only be associated with a single Opportunities record.
Each Milestones record has a Workload values associated with it.

Desired Output

I would like to be able to display # of Opportunties records based on the # of unique Workloads per Opportunities record. Like this:

I believe I need to add a new column (Unique Workloads pe Opportunity) but can't figure out the proper syntax.

Any help would be appreciated.

Re: Help with calculating the # of records in one table based on criteria from a second table

VillyMBI — Wed, 28 Feb 2024 07:16:16 GMT

Try adding new DAX measure in the table.
like

Opp per Workload =

VAR _Worload = SELECTEDVALUE(Milestone[Workload])

RETURN CALCULATE( DISTINCTCOUNT([OpportunityID]), FILTER(Milestone, [Workload] = _Worload))

I hope this will help you.