topic Help needed- Distinct count with filter and multiple dates in DAX Commands and Tips

Help needed- Distinct count with filter and multiple dates

jgarcin8 — Thu, 20 Apr 2023 02:24:30 GMT

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

Re: Help needed- Distinct count with filter and multiple dates

Mahesh0016 — Thu, 20 Apr 2023 07:40:41 GMT

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.

Re: Help needed- Distinct count with filter and multiple dates

jgarcin8 — Thu, 20 Apr 2023 14:56:40 GMT

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

Re: Help needed- Distinct count with filter and multiple dates

Mahesh0016 — Fri, 21 Apr 2023 07:30:11 GMT

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

Re: Help needed- Distinct count with filter and multiple dates

jgarcin8 — Sat, 22 Apr 2023 04:22:19 GMT

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.

Re: Help needed- Distinct count with filter and multiple dates

Anonymous — Tue, 25 Apr 2023 03:14:52 GMT

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.

Re: Help needed- Distinct count with filter and multiple dates

jgarcin8 — Tue, 23 May 2023 16:56:48 GMT

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