DistinctCount values with max date

[Divous]

Hi community,

I am trying achieve probably simple thing. I have table:

Order of state is:
1. in_progress

2. lost
3. done

Main goal is to know how many users are in which state = their last state = aaa and ccc is done; bbb is lost.

If you have some tips for nice visualization of their flow/funnel it will be great. And/or some pie chart with "% of users who started is still in_progress, % reach done,.."  🙂
Tricky part for me is that user can have multiple same state, but I need to calculate only last.

I am trying to improve my measure, but without success for now

users_done = CALCULATE(DISTINCTCOUNT('mytable'[owner_id]),FILTER('mytable','mytable'[state]="done"))

Thanks in advance!

[v-yalanwu-msft · Accepted solution]

Hi @Divous ,

We could first create a measure (date_max) to calculate the last time of each user, then calculate the number of users in different status.

step 1: Add measure (date_max)

date_max =
CALCULATE (
    MAX ( 'max date'[date] ),
    FILTER ( ALL ( 'max date' ), 'max date'[user] = MAX ( 'max date'[user] ) )
)

step 2: Add measure(count )

count =
CALCULATE (
    COUNTROWS ( 'max date' ),
    FILTER ( 'max date', [date] = [date_max] )
)

The final output is shown below:

Best regards,
Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Why should the answer for bbb be lost.  Shouldn't it be in_progress?  Also, share data in a form that can be pasted in an MS Excel file.

[Divous]

Hi @Ashish_Mathur,

thanks for your response!

My table was just a simplified example, sorry. So I'm attaching original data, from which it will be clearer:
https://ufile.io/fg3zpaog

There may be the same status for the user, as the process may start multiple times - sometimes end in done, other times in_progress, or fail at all (lost), or he finished, but without data (no_data).

[Ashish_Mathur]

Hi,

On a smaller dataset show me the expected result in another tab of the Excel file.