Power BI Interactive Employee Performance Dashboard
Budget: ₹600 – ₹1,500 INR
Project Overview
We are looking to hire a skilled Power BI developer to build an interactive dashboard that visualizes employee task compliance. We have an existing web-based report created in Flask, and we need you to replicate its exact data logic, filtering capabilities, and visual layout within Power BI.
The primary data source is a Microsoft SQL Server database. The goal is to transform raw task data into a meaningful, color-coded "heatmap" that allows managers to quickly assess performance, track task statuses, and identify issues.
Data Source
You will be provided with read-only access to our MS SQL Server database. The key tables for this report are:
basecamp.cm_heatmap (Main transaction table with task instances)
basecamp.cm_heatmap_masters_list (Master list of task details)
basecamp.checklist_proof (Records of submitted proofs for tasks)
basecamp.distinct_enames (List of employees and their departments)
basecamp.distinct_department (List of departments)
Core Requirement: Replicate Existing Business Logic
The most critical part of this task is to precisely replicate the business logic embedded in our existing system's SQL query. This logic transforms raw data into a final, calculated status for each task. You will need to implement this transformation using Power Query (M language) and/or DAX.
The final calculated status (referred to as legend_status) is determined by the following hierarchy of rules:
Anomaly:
A task is marked "Completed" but requires proof, and no proof was submitted.
OR a task is marked "Not Started" or "Dropped", but proof was submitted for it.
Flagged:
Any proof associated with the task has been marked as flagged (is_flagged = 1). This status overrides all others except "Anomaly".
Audited (On Time / Delay):
A proof has been successfully audited (is_audited = 1).
The status is further categorized as "Audited (On Time)" if the task was completed on or before its due date, or "Audited (Delay)" if completed after.
Audit Pending (On Time / Delay):
A proof has been submitted, but it has not yet been audited (is_audited = 0).
This is categorized as "Audit Pending (On Time)" or "Audit Pending (Delay)" based on the task completion date versus its due date.
Completed (On Time / Delay):
Applies to tasks that do not require proof.
Status is "Completed (On Time)" or "Completed (Delay)" based on the completion date versus its due date.
Not Started: The task has not been started.
Dropped: The task was dropped or cancelled.
(To ensure perfect accuracy, the original SQL query used in our web application will be provided as a reference.)
Dashboard Features & Visuals
The Power BI report must include the following components:
1. Filters / Slicers:
Date Range: Allow the user to select a custom start and end date.
Employee: Multi-select slicer for employee names.
Department: Multi-select slicer for departments.
Calculated Status: Multi-select slicer based on the final legend_status values (e.g., "Flagged", "Anomaly", "Audit Pending").
Proof Type: Slicer to filter for tasks that require "Proof" vs. "No Proof".
2. Main Visual - Heatmap Matrix:
Rows: Employee Name, then Task Description (hierarchical).
Columns: Dates.
Values: A visual indicator (e.g., a colored icon or conditional formatting on a shape/character) representing the calculated legend_status for that task on that day. The color-coding should be:
Red: For critical issues (Not Started, Dropped, Anomaly, Flagged).
Yellow: For items needing attention (Delayed tasks).
Green: For tasks completed successfully and on time.
3. KPI Cards / Summary View:
Display key metrics that update based on filter selections:
Total Tasks
Count of "Completed (On Time)"
Count of "Completed (Delay)"
Count of "Anomalies"
Count of "Flagged"
Count of "Audit Pending"
4. Tooltips & Interactivity:
When a user hovers over a cell in the heatmap matrix, a tooltip must appear showing detailed information for that specific task instance:
Full Task Description
Employee Name
Due Date
Status & Reason (especially for anomalies)
Hyperlinks to any submitted proofs.
GeneratedTaskID and MasterTaskID.
(Optional - Advanced Feature): We are interested in the ability to update a task's status (e.g., mark as "Audited" or "Flagged") directly from the report. Please advise if this is feasible via Power Apps integration and scope it as a separate, optional deliverable.
Deliverables
The final Power BI file (.pbix).
Brief documentation explaining the Power Query steps and any complex DAX measures used to implement the business logic.
We are looking to hire a skilled Power BI developer to build an interactive dashboard that visualizes employee task compliance. We have an existing web-based report created in Flask, and we need you to replicate its exact data logic, filtering capabilities, and visual layout within Power BI.
The primary data source is a Microsoft SQL Server database. The goal is to transform raw task data into a meaningful, color-coded "heatmap" that allows managers to quickly assess performance, track task statuses, and identify issues.
Data Source
You will be provided with read-only access to our MS SQL Server database. The key tables for this report are:
basecamp.cm_heatmap (Main transaction table with task instances)
basecamp.cm_heatmap_masters_list (Master list of task details)
basecamp.checklist_proof (Records of submitted proofs for tasks)
basecamp.distinct_enames (List of employees and their departments)
basecamp.distinct_department (List of departments)
Core Requirement: Replicate Existing Business Logic
The most critical part of this task is to precisely replicate the business logic embedded in our existing system's SQL query. This logic transforms raw data into a final, calculated status for each task. You will need to implement this transformation using Power Query (M language) and/or DAX.
The final calculated status (referred to as legend_status) is determined by the following hierarchy of rules:
Anomaly:
A task is marked "Completed" but requires proof, and no proof was submitted.
OR a task is marked "Not Started" or "Dropped", but proof was submitted for it.
Flagged:
Any proof associated with the task has been marked as flagged (is_flagged = 1). This status overrides all others except "Anomaly".
Audited (On Time / Delay):
A proof has been successfully audited (is_audited = 1).
The status is further categorized as "Audited (On Time)" if the task was completed on or before its due date, or "Audited (Delay)" if completed after.
Audit Pending (On Time / Delay):
A proof has been submitted, but it has not yet been audited (is_audited = 0).
This is categorized as "Audit Pending (On Time)" or "Audit Pending (Delay)" based on the task completion date versus its due date.
Completed (On Time / Delay):
Applies to tasks that do not require proof.
Status is "Completed (On Time)" or "Completed (Delay)" based on the completion date versus its due date.
Not Started: The task has not been started.
Dropped: The task was dropped or cancelled.
(To ensure perfect accuracy, the original SQL query used in our web application will be provided as a reference.)
Dashboard Features & Visuals
The Power BI report must include the following components:
1. Filters / Slicers:
Date Range: Allow the user to select a custom start and end date.
Employee: Multi-select slicer for employee names.
Department: Multi-select slicer for departments.
Calculated Status: Multi-select slicer based on the final legend_status values (e.g., "Flagged", "Anomaly", "Audit Pending").
Proof Type: Slicer to filter for tasks that require "Proof" vs. "No Proof".
2. Main Visual - Heatmap Matrix:
Rows: Employee Name, then Task Description (hierarchical).
Columns: Dates.
Values: A visual indicator (e.g., a colored icon or conditional formatting on a shape/character) representing the calculated legend_status for that task on that day. The color-coding should be:
Red: For critical issues (Not Started, Dropped, Anomaly, Flagged).
Yellow: For items needing attention (Delayed tasks).
Green: For tasks completed successfully and on time.
3. KPI Cards / Summary View:
Display key metrics that update based on filter selections:
Total Tasks
Count of "Completed (On Time)"
Count of "Completed (Delay)"
Count of "Anomalies"
Count of "Flagged"
Count of "Audit Pending"
4. Tooltips & Interactivity:
When a user hovers over a cell in the heatmap matrix, a tooltip must appear showing detailed information for that specific task instance:
Full Task Description
Employee Name
Due Date
Status & Reason (especially for anomalies)
Hyperlinks to any submitted proofs.
GeneratedTaskID and MasterTaskID.
(Optional - Advanced Feature): We are interested in the ability to update a task's status (e.g., mark as "Audited" or "Flagged") directly from the report. Please advise if this is feasible via Power Apps integration and scope it as a separate, optional deliverable.
Deliverables
The final Power BI file (.pbix).
Brief documentation explaining the Power Query steps and any complex DAX measures used to implement the business logic.