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.
Dear all,
I'm trying to create a calculated column ('Event_Count') that would sum up all "1" of column "Event" based on the month-year and on the operator. For example, below is January 2017 data. The sum of the "1" in Event is 2 for January 2017 and Operator A. The column 'Event_Month' should just count the total for the matching month-year and opeator at every cell. Does anyone know how to do it?
Date | Event | Operator | Event_Count |
1-Jan-17 | 0 | ||
2-Jan-17 | 0 | ||
3-Jan-17 | 0 | ||
4-Jan-17 | 0 | ||
5-Jan-17 | 1 | A | 2 |
6-Jan-17 | 0 | ||
7-Jan-17 | 0 | ||
8-Jan-17 | 0 | ||
9-Jan-17 | 1 | B | 4 |
10-Jan-17 | 0 | ||
11-Jan-17 | 0 | ||
12-Jan-17 | 1 | A | 2 |
13-Jan-17 | 0 | ||
14-Jan-17 | 0 | ||
15-Jan-17 | 1 | B | 4 |
16-Jan-17 | 0 | ||
17-Jan-17 | 0 | ||
18-Jan-17 | 0 | ||
19-Jan-17 | 1 | B | 4 |
20-Jan-17 | 1 | C | 2 |
21-Jan-17 | 0 | ||
22-Jan-17 | 0 | ||
23-Jan-17 | 0 | ||
24-Jan-17 | 1 | C | 2 |
25-Jan-17 | 0 | ||
26-Jan-17 | 0 | ||
27-Jan-17 | 0 | ||
28-Jan-17 | 0 | ||
29-Jan-17 | 0 | ||
30-Jan-17 | 1 | B | 4 |
31-Jan-17 | 0 |
Thank you.
Solved! Go to Solution.
Hi @Anonymous
Try this Calculated Column
EventCount = COUNTROWS ( FILTER ( TableName, TableName[Operator] = EARLIER ( TableName[Operator] ) && TableName[Event] = 1 && MONTH ( TableName[Date] ) = MONTH ( EARLIER ( TableName[Date] ) ) && YEAR ( TableName[Date] ) = YEAR ( EARLIER ( TableName[Date] ) ) ) )
Does it need to be in 1 column? And are you allowed to use measures?
Hi @Anonymous
Try this Calculated Column
EventCount = COUNTROWS ( FILTER ( TableName, TableName[Operator] = EARLIER ( TableName[Operator] ) && TableName[Event] = 1 && MONTH ( TableName[Date] ) = MONTH ( EARLIER ( TableName[Date] ) ) && YEAR ( TableName[Date] ) = YEAR ( EARLIER ( TableName[Date] ) ) ) )
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |