DISTINCT COUNT - From Another Table as String

[Dbrunts0888_]

Hi All,

I have been at this for hours and looking thorugh other post's nothing quite works.

Hours_Spend_Data - is my data table which contains all weeks worked, hours, charges by site / colleague

Colleague_Tenure - is a new calculated table created to store colleague length of service / weeks worked

I have created the relationship and it works as intended when creating a matrix it shows colleagues and their weeks worked values.

However, I need to get those distinct counts added to the Colleague_Tenure lookup so I can add more columns 

e.g. IF > 12 weeks worked "Post Parity", "Pre Parity"

(I have created a weeks worked column to give distinct weekly values as some colleagues have multiple timesheets)

I have tried groupby, count, distinct count etc. but nothing is working as I need.

If anyone needs more information let me know.

Thanks

Dan

Colleague_Tenure

Hours_Spend_Data

[Dbrunts0888_]

I'm 99.9% sure I tried calculate count and calculate distinct count.

will triple check in the morning.

[AlexisOlson]

Maybe you tried it without CALCULATE. That function needs to be included so that it does a context transition and filters Hours_Spend_Data by the current row rather than calculating the distinct count over the entire table.

[AlexisOlson · Accepted solution]

How about this?

CALCULATE ( DISTINCTCOUNT ( Hours_Spend_Data[Weeks_Worked] ) )