Count Distinct users per date but group result by rank of users

[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 simple 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!

[v-lili6-msft · Accepted solution]

hi @peterver 

For your case, adjust your model as below:

1. Create a relationship between between two tables by [User] column.

2. Adjust the measure as below:

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

3. Create a visual that contains [Rank] column,[Date] column and this measure.

and here is my sample pbix file, please try it.

Regards,

Lin

[Greg_Deckler]

Just include your Rank column in your visual?