Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Can you someone help me count # of occurrence by date? Please see the date below.
Original Data:
ID | Date |
AAA | 9/28/19 1:00 AM |
BBB | 9/28/19 2:00 AM |
AAA | 9/28/19 3:00 AM |
AAA | 9/28/19 4:00 AM |
CCC | 9/28/19 5:00 AM |
CCC | 9/29/19 1:00 AM |
Adding # of Occurrence column to count IDs # of times appear on the data table.
ID | Date | # of occurrence |
AAA | 9/28/19 1:00 AM | 1 |
BBB | 9/28/19 2:00 AM | 1 |
AAA | 9/28/19 3:00 AM | 2 |
AAA | 9/28/19 4:00 AM | 3 |
CCC | 9/28/19 5:00 AM | 1 |
CCC | 9/29/19 1:00 AM | 2 |
When I filter AAA, I like to see the following.
ID | Date | # of occurrence |
AAA | 9/28/19 1:00 AM | 1 |
AAA | 9/28/19 3:00 AM | 2 |
AAA | 9/28/19 4:00 AM | 3 |
I couldn't find by searching online. Please help. Thanks!
Solved! Go to Solution.
Hi @EZiamslow
Try this
1. Create a column with only the date (excluding time):
DateOnly = INT(Table1[Date])
2. Create the occurrence column:
# of occurrence = CALCULATE ( COUNT ( Table1[ID] ), ALLEXCEPT ( Table1, Table1[ID], Table1[DateOnly] ), Table1[Date] <= EARLIER ( Table1[Date] ) )
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
Hi @EZiamslow
Try this
1. Create a column with only the date (excluding time):
DateOnly = INT(Table1[Date])
2. Create the occurrence column:
# of occurrence = CALCULATE ( COUNT ( Table1[ID] ), ALLEXCEPT ( Table1, Table1[ID], Table1[DateOnly] ), Table1[Date] <= EARLIER ( Table1[Date] ) )
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |