Most efficient way to filter a measure on a related table

[BobKoenen]

HI you all,

I want to filter a measure on a variable from my dim. table, because my data table had a lot of records I want to structure it the most efficient way. 

My data table looks like this 

EMployee	Project	Hours
Joe	A	1
Joe	A	4
Joe	B	2
Joe	C	2
Joe	C	4

I want to know how many Projects an employee has worked on. I created the measure     NR.ofproject = DISTINCTCOUNT(data,project)   Which works perfectly. 

Now I want to filter this measure based on my dimension table 

Project	Project Type
A	Free
B	Free
C	Paid

So I tried to create a measure Nr. of Free projects = CALCULATE(NR. OF PROJECTS, FILTER(RELATED(PROJECTS, PROJECTTYPE = "Free")))  But it does not work.

Because my data table consists out of + 1m records I would like to create an as fast as possible measure.  

Can you help me?

[v-xuding-msft]

Hi @BobKoenen ,

You can filter directly by using the slicers, because your two tables have a one-to-many relationship.

Or use @ryan_mayu’s suggestion.

[ryan_mayu · Accepted solution]

@BobKoenen 

maybe you can try this

Measure = CALCULATE(DISTINCTCOUNT('Table'[Project]),FILTER('Table (2)','Table (2)'[Project Type]="Free"))