Count Distinct users per date, but group by rank of users returned

[peterver]

I have a table with time booking data like - 

Date               User          Activity            Hours
01/01/2019    Name A    Work                5
01/01/2019    Name A    Annual Leave   5
01/01/2019    Name B    Work               10
01/01/2019    Name C    Work               10
02/01/2019    Name A    Work               10
02/01/2019    Name D    Annual Leave  10
02/01/2019    Name C    Work               12

And a lookup table with ranks like -

User          Rank
Name A    Manager
Name B    Manager
Name C    Associate
Name D    Employee

I want create a measure, or a combination of measures, to count the distinct number of users per date, but return the count of each rank per date.

So for 01/01/2019 it would return Manager = 2, Associate = 1

for 02/01/2019 it would return Manager = 1, Associate = 1, Employee = 1

I can get a count of the distinct users per date with a measure like this - 

Count of User total for Date = 
CALCULATE(
DISTINCTCOUNT('Table'[User]),
ALLSELECTED('Table'[Date])
)

Is it possible to return a count of the rank per date based on distinct users per date? 

Thanks for any help!

[peterver]

Thanks for the feedback!

I am hoping to capture it in a measure though and here is why -

I want to prescribe targets for working hours which should be hit based on who worked on a given day, with weights like - 

Role          % of hours which should be "work"

Manager  70%

Employee 90%

Associate 100%

So that on a day where 1 manager, 2 employees, and 3 associates work, the blended target should be ((1 x 70) + (2 x 90) + (3 x 100)) / 6 = 91.66%

Then i can prorate this out over a month/year/etc. and determine on average what the target working hours were for the people who worked versus what was actually worked

maybe should have included this detail in the post

[PaulDBrown · Accepted solution]

@peterver 

Try this:

Distinct Ranks = CALCULATE(COUNTA('Rank'[Rank]); 
                  TREATAS(VALUES(Users[User]); 'Rank'[User]))

Which gets you this:

[amitchandak]

Join the two tables user.

Have one measure

users = DISTINCTCOUNT('Table'[User])

Create a matrix and drag date on row, Rank on column and users on values.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin