Help needed- Distinct count with filter and multiple dates

[jgarcin8]

Hi

I have this table: 

I need 3 things:

-Distinct count of [user_id], filter by [acknowledgement_status]="Complete" , based on [completion_date]

-Count of [user_id], filter by [acknowledgement_status]="Complete" , based on [completion_date]

-Distinct count of [user_id], [acknowledgement_status]="All", based on [distribution_date]

Can someone help me , please?

user_id	distribution_date	completion_date	acknowledgement_status
23377E147C5A470JSLKLSDKLKFS	12/15/2020 12:47:46 PM	12/17/2020 2:20:48 PM	Complete
2C4BB989BB69JKFAKJFAKLJFLDAS	8/21/2020 10:24:01 AM	9/18/2020 4:06:47 PM	Complete
D4773A2C685845CFAA9FC923C0743987	1/11/2021 1:57:04 PM	1/11/2021 1:58:43 PM	Complete
D54C6B9787F4413DACEE1AF8B09526FE	6/21/2019 1:48:25 PM	7/1/2019 10:04:44 AM	Complete
D4A35F9003E34DD08303613C29359E8C	1/8/2021 12:05:38 PM	1/11/2021 9:16:34 AM	Complete
0C74A991F0704BEDB28BAD6CA83D1B0F	6/30/2020 3:33:30 PM	6/30/2020 3:59:10 PM	Complete

[Mahesh0016 · Accepted solution]

Hello @jgarcin8
i think you need Distinct count of [user_id] base on [acknowledgement_status] not [completion_date] and [distribution_date]. but you need in Count of [user_id] base on  [completion_date] and [distribution_date].Please guide further.

Distinct count based on completion_date =
CALCULATE (
DISTINCTCOUNT ( 'Distinct Count'[user_id] ),
'Distinct Count'[acknowledgement_status] = "Complete"
)
####################################################
count based on completion_date =
CALCULATE (
COUNT ( 'Distinct Count'[distribution_date] ),
ALLEXCEPT ( 'Distinct Count', 'Distinct Count'[completion_date] ),
'Distinct Count'[acknowledgement_status] = "Complete"
)
###################################################

count based on distribution_date =
CALCULATE (
COUNT ( 'Distinct Count'[user_id] ),
'Distinct Count'[acknowledgement_status] = "All"
)

@jgarcin8 I hope this help you.
 

[v-rzhou-msft · Accepted solution]

Hi @jgarcin8 ,

I suggest you to add two columns to convert distribution date and completion date from datetime to date format.

distribution only date = DATEVALUE('Table'[distribution_date]) 

completion only date = DATEVALUE('Table'[completion_date])

Then create a calendar table.

Calendar = 
ADDCOLUMNS (
    CALENDARAUTO (),
    "Year", YEAR ( [Date] ),
    "Quarter",
        "Q" & ""
            & QUARTER ( [Date] ),
    "Month", FORMAT ( [Date], "MMMM" ),
    "MonthSort", MONTH ( [Date] )
)

Create two inactive relationship between tables.

Measure:

Measure1 = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[user_id] ),
    USERELATIONSHIP ( 'Calendar'[Date], 'Table'[completion only date] ),
    'Table'[acknowledgement_status] = "Complete"
)

Measure2 = 
CALCULATE (
    COUNT ( 'Table'[user_id] ),
    USERELATIONSHIP ( 'Calendar'[Date], 'Table'[completion only date] ),
    'Table'[acknowledgement_status] = "Complete"
)

Measure3 = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[user_id] ),
    USERELATIONSHIP ( 'Calendar'[Date], 'Table'[distribution only date] ),
    USERELATIONSHIP ( 'Table'[distribution only date], 'Calendar'[Date] ),
    'Table'[acknowledgement_status] = "All"
)

Result is as below.

Best Regards,
Rico Zhou

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

[jgarcin8]

Thank you, this really helped with my request! Appreciate the support and the quick response with a solution!

[jgarcin8]

Hi @Mahesh0016 

It's close but what Im looking for is:

-How many unique users do we have per month (based on [completion date]) and filter by acknowledgement_status]="Complete".

-How many users do we have per month (based on [completion date]) and filter by acknowledgement_status]="Complete".

-How many unique users do we have per month (based on [distribution date]) and filter by acknowledgement_status]="All".

So I can graph the 3 metrics in this visual:

Do you have any suggestions? I really appreciate your help. Thank you

[Mahesh0016]

@jgarcin8  do you have month  column base on [completion date] / [distribution date] in your table.

[jgarcin8]

Hi @Mahesh0016 - I don't have it. Every user can have a completion date and also a distribution date, but both dates are in separate columns.

[Mahesh0016 · Accepted solution]

Hello @jgarcin8
i think you need Distinct count of [user_id] base on [acknowledgement_status] not [completion_date] and [distribution_date]. but you need in Count of [user_id] base on  [completion_date] and [distribution_date].Please guide further.

Distinct count based on completion_date =
CALCULATE (
DISTINCTCOUNT ( 'Distinct Count'[user_id] ),
'Distinct Count'[acknowledgement_status] = "Complete"
)
####################################################
count based on completion_date =
CALCULATE (
COUNT ( 'Distinct Count'[distribution_date] ),
ALLEXCEPT ( 'Distinct Count', 'Distinct Count'[completion_date] ),
'Distinct Count'[acknowledgement_status] = "Complete"
)
###################################################

count based on distribution_date =
CALCULATE (
COUNT ( 'Distinct Count'[user_id] ),
'Distinct Count'[acknowledgement_status] = "All"
)

@jgarcin8 I hope this help you.