I am trying to build a dashboard with:
1) a KPI that is the count of notifications raised by employees in a certain department
2) a bar graph that shows the count of notifications raised by employees in a certain department by employee location and notification type
3) a pie chart that shows the breakdown of the types of notifications raised by employees in a certain department
The data I have is a list of all notifications raised by all employees (see sample "All Notifications") and a list of employees from the department I am interested in (see sample "Employee Data").
I have tried using this script to determine which notifications were raised by the employees in this department:
AllNotifications:
LOAD
Notification,
Notif.date,
"Notifictn type",
"Created on",
"Changed on",
"Created by" as [User Name],
EmployeeNotifications:
LOAD
"User Name",
"Employee Name",
"Location"
FROM [Employee Data]
MapEmployeeNotifications:
MAPPING LOAD
[User Name],
'1'
RESIDENT EmployeeNotifications;
New_Table:
LOAD
[User Name],
ApplyMap('MapEmployeeNotifications',[User Name],'0') AS RaisedByEmployee
RESIDENT AllNotifications;
If I insert a table into my dashboard with the columns "Employee Name" and "RaisedbyEmployee", it shows that 4 employees raised notifications.
When I use the following expression in the KPI field: count (distinct if( [RaisedByField]='1',[User Name])) I also get a count of 4 employees raising notifications.
The issue I am facing is that when I use this expression: count({<RaisedByField={'1'}>}[RaisedByField]) I get a count of 10 employees raising notifications when I know from my raw data then there were actually only 4. This is causing my bar graph and pie chart to also be incorrect. For the bar graph my expressions were:
sum (if(RaisedByField='1' and [Notifictn type]='DR' ,1,0)) for each of the notification types
For my pie chart the expression was:
count({<RaisedByField={'1'}>}[Notifictn type])
How can I correct the script to make sure that I am getting the correct counts of notifications raised by employees? !
