Currently, with the DB settings below, I can filter proposals by fiscal year and by employee and department. However, I am struggling to filter proposals by employee and department for each fiscal year (employees, departments, and fiscal years do not have a direct relationship).
Could you please provide any solutions?
- View: Dash Bord (Type: Table, chart)
- Filtering of proposals is OK in the order of fiscal year โ proposal.
- Filtering of proposals is NG in the order of fiscal year โ employee.
- Filtering of proposals is NG in the order of fiscal year โ department.
ใDB Configurationใ
โ Fiscal Year
Fiscal Year ID
Fiscal Year Name
โ Proposal Record
Proposal Record ID
Proposal Record Name
Proposal Content
Proposer (REF: Employee)
Implementer (REF: Employee)
Last Update Date and Time
Status
Submission Fiscal Year (REF: Fiscal Year)
- Submission Fiscal Year FORMULA
IF(
MONTH([Last Update Date and Time]) >= 5,
YEAR([Last Update Date and Time]),
YEAR([Last Update Date and Time]) - 1
)
โ Employee
Employee ID
Employee Name
Affiliated Department (REF: Department)
CV_Proposal Count
CV_Implementation Count
- Proposal Count FORMULA
COUNT(SELECT(Proposal Record[Proposer], AND([_THISROW].[Employee ID] = [Proposer], [Status] = "Improvement Report Completed")))
- Implementation Count FORMULA
COUNT(SELECT(Proposal Record[Implementer], AND([_THISROW].[Employee ID] = [Implementer], [Status] = "Improvement Report Completed")))
โ Department
Department ID
Department Name
LinkedIn
Twitter
