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 = 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!

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.

4 Replies

  • 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.

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    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.