HiI 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_iddistribution_datecompletion_dateacknowledgement_status23377E147C5A470JSLKLSDKLKFS12/15/2020 12:47:46 PM12/17/2020 2:20:48 PMComplete2C4BB989BB69JKFAKJFAKLJFLDAS8/21/2020 10:24:01 AM9/18/2020 4:06:47 PMCompleteD4773A2C685845CFAA9FC923C07439871/11/2021 1:57:04 PM1/11/2021 1:58:43 PMCompleteD54C6B9787F4413DACEE1AF8B09526FE6/21/2019 1:48:25 PM7/1/2019 10:04:44 AMCompleteD4A35F9003E34DD08303613C29359E8C1/8/2021 12:05:38 PM1/11/2021 9:16:34 AMComplete0C74A991F0704BEDB28BAD6CA83D1B0F6/30/2020 3:33:30 PM6/30/2020 3:59:10 PMComplete

Hello jgarcin8i 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.

Forum Discussion

jgarcin8

Frequent Visitor

3 years ago

Solved

Help needed- Distinct count with filter and multiple dates

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

DAX

Mahesh0016
3 years ago
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.

Anonymous

3 years ago

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.

Help needed- Distinct count with filter and multiple dates.pbix93 KB

6 Replies

Mahesh0016
Super User
3 years ago
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.
jgarcin8
Frequent Visitor
3 years ago
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
  Super User
  3 years ago
  jgarcin8 do you have month column base on [completion date] / [distribution date] in your table.
  - jgarcin8
    Frequent Visitor
    3 years ago
    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.

Anonymous

Not applicable

3 years ago

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.

Help needed- Distinct count with filter and multiple dates.pbix93 KB

jgarcin8
Frequent Visitor
3 years ago
Thank you, this really helped with my request! Appreciate the support and the quick response with a solution!

Forum Discussion

Help needed- Distinct count with filter and multiple dates

6 Replies

Related content

distinct count of multiple columns

Getting distinct values for multiple columns

Distinct Count with multiple conditions

count distinct

Distinct count with multiple filters