Forum Discussion

Divous's avatar
Divous
Helper III
5 years ago
Solved

DistinctCount values with max date

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...
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    5 years ago

    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's avatar
Ashish_Mathur
Super User
5 years ago

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's avatar
Divous
Helper III
5 years ago

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's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Hi,

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