Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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.
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.
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.
Thank you, this really helped with my request! Appreciate the support and the quick response with a solution!
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
@jgarcin8 do you have month column base on [completion date] / [distribution date] in your table.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
76 | |
50 | |
46 | |
16 | |
12 |