Forum Discussion

jgarcin8's avatar
jgarcin8
Frequent Visitor
3 years ago
Solved

Help needed- Distinct count with filter and multiple dates

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_iddistribution_datecompletion_dateacknowledgement_status
23377E147C5A470JSLKLSDKLKFS12/15/2020 12:47:46 PM12/17/2020 2:20:48 PMComplete
2C4BB989BB69JKFAKJFAKLJFLDAS8/21/2020 10:24:01 AM9/18/2020 4:06:47 PMComplete
D4773A2C685845CFAA9FC923C07439871/11/2021 1:57:04 PM1/11/2021 1:58:43 PMComplete
D54C6B9787F4413DACEE1AF8B09526FE6/21/2019 1:48:25 PM7/1/2019 10:04:44 AMComplete
D4A35F9003E34DD08303613C29359E8C1/8/2021 12:05:38 PM1/11/2021 9:16:34 AMComplete
0C74A991F0704BEDB28BAD6CA83D1B0F6/30/2020 3:33:30 PM6/30/2020 3:59:10 PMComplete
DAX
  • Mahesh0016's avatar
    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's avatar
    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.

     

6 Replies

  • Mahesh0016's avatar
    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's avatar
    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's avatar
      Mahesh0016
      Super User
      3 years ago

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

       

      • jgarcin8's avatar
        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's avatar
    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.

     

    • jgarcin8's avatar
      jgarcin8
      Frequent Visitor
      3 years ago

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