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.

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.