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

[festazen]

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.

[VillyMBI]

HI @festazen ,

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.