need help getting count of "ID" within another ID's count DAX HELP

[Anonymous]

Hi,

    I have a relationship between two tables, a "DEV" table and "CONTRACT" table. There are Development ID's and Contract ID's. Each Contract has a Dev ID. 

I've created a measure that shows current "OPEN Dev's" in the last 30 days. Now, I want to show how many CONTRACTS are created within those "OPEN Dev's". How can I handle this in a measure? I need the total of CONTRACTS created within those OPEN devs within the last 30 days. 

Measure that gets count of "OPEN DEV's in Last 30days":

L30DaysDev = CALCULATE(count('dw DimDevelopment'[DevelopmentID]),filter(all('dw DimDevelopment'),'dw DimDevelopment'[DevCreatedDate].[Date] >= TODAY()-30),filter(all('dw DimDevelopment'),'dw DimDevelopment'[DevIsClosed]="Open"),filter(all('dw DimDevelopment'),'dw DimDevelopment'[DevParentID]=blank()))

Below is the SQL Script that gets me the right count(without the last 30days filter), although it is grouped by the devID (which I dont want, I just need to get the total count).

select D.dimdevelopmentkey, count(F.dimcontractscopekey)  from dw.FactContractScope as F right join dw.dimdevelopment as D on F.dimdevelopmentkey = D.dimdevelopmentkey where d.devisclosed = 'Open'  group by D.dimdevelopmentkey

Any help would be greatly appreciated. Thanks!!

[v-frfei-msft]

Hi @Anonymous ,

Could you please share your sample data and excepted result to me if you don't have any Confidential Information.Please upload your files to One Drive and share the link here.

[Anonymous · Accepted solution]

Thank you for getting back to me. However, I figured out the solution myself. Thank you anyway.