Show TImesheet lines where Timesheet Nbr matches all Timesheet Nbrs for a Project's Approved Lines

[Planet3000]

Question:

How to identify , in Power BI Table Visual, all timesheet lines that are blocking Approved timesheet lines from Posting?

The Table visual will display all TImesheet lines for all projects where Timesheet Nbr matches all Timesheet Nbrs for a Project's Approved Lines

There is a table with timesheet lines with columns Timesheet Number, Project Name, Timesheet Line Status, Hours, Emp Id.

Timesheet Line Status can have the value 'Draft', 'In Review', 'Returned', Approved' or 'Posted'.

On a single timesheet (unique TImesheet Number), there can be several timesheet lines entered.

When all timesheet lines on a timesheet have all been Approved, then Line Status updates to Posted otherwise the Line Status will depend on whether the timesheet line has been submitted, approved or rejected.  

How do I achieve the outcome whereby selecting e.g. Project A in a slicer [Project Name] will display in a table visual all non-posted lines for all projects (where Timesheet Line Status is any status except for Posted) where the TImesheet number for Project A's Approved lines matches TImesheet number for any other project?

[Planet3000]

Desired Outcome: When a particular project, say "Green" is selected in the slicer, then the table visual should be filterd to only show Approved Timesheet Line Status for project Green AND all timesheet lines for all other projects which share the same Timesheet Number as an Approved line for the selected Project.

So the Table above would have:

- timesheet line TS-0002 for project 0004-K , Red, filtered out as it is Approved for another project and not blocking Green project lines from being Posted.

- timesheet line TD-0003 for project 0001-M , Green, filtered out as it is Posted.

[v-dineshya]

Hi @Planet3000 ,

Thanks for reaching out to the Microsoft fabric community forum. 

Please follow below steps:

1. Create a slicer on Proj Name — users select the project (e.g., Green).
2. Create a calculated table or use DAX logic to identify Timesheet Nbrs that are blocking posting for the selected project.

A. Create a measure:

ApprovedTimesheetNbrsForSelectedProject =
CALCULATETABLE (
VALUES ( TSTable[Timesheet Nbr] ),
TSTable[Timesheet Line Status] = "Approved",
TSTable[Proj Name] IN SELECTEDVALUE(TSTable[Proj Name])
)

Note: This gives you the Timesheet Nbrs where the selected project has an Approved line.

B. Create another measure: ShowFlag

ShowBlockingLines =
VAR SelectedProject = SELECTEDVALUE(TSTable[Proj Name])
VAR TimesheetsWithApprovedSelectedProject =
CALCULATETABLE (
VALUES (TSTable[Timesheet Nbr] ),
TSTable[Proj Name] = SelectedProject,
TSTable[Timesheet Line Status] = "Approved"
)
VAR IsInRelevantTimesheet =
TSTable[Timesheet Nbr] IN TimesheetsWithApprovedSelectedProject
VAR IsNotPosted = TSTable[Timesheet Line Status] <> "Posted"

RETURN
IF ( IsInRelevantTimesheet && IsNotPosted, 1, 0 )

C. Use this in your Table Visual: Add all your columns (Timesheet Nbr, Line Status, Proj ID, etc.). Apply a visual-level filter: ShowBlockingLines = 1.

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards

[v-dineshya]

Hi @Planet3000 ,

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

[v-dineshya]

Hi @Planet3000 ,

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

[v-dineshya]

Hi @Planet3000 ,

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

[Planet3000]

Sample Data:

Timesheet Nbr	Timesheet Line Status	Proj ID	Proj Name	ProjTransDate	Hours	Emp ID	Emp Name
TS-0001	Approved	0001-M	Green	1/04/2024	4	E001	Jo Smith
TS-0001	In Review	0002-D	Yellow	2/04/2024	3	E001	Jo Smith
TS-0001	In Review	0003-M	Blue	3/04/2024	2	E001	Jo Smith
TS-0002	Approved	0001-M	Green	4/04/2024	5	P002	Jamie Walker
TS-0002	Returned	0002-D	Yellow	5/04/2024	8	P002	Jamie Walker
TS-0002	Approved	0004-K	Red	6/04/2024	6	P002	Jamie Walker
TD-0003	Posted	0001-M	Green	6/03/2024	4	E001	Jo Smith

[Planet3000]

Query: 

How to identify , in Power BI Table Visual, all timesheet lines that are blocking Approved timesheet lines from Posting?

Issue:

On a Timesheet, employees enter line with hours worked per project per week.

The Timesheet Line status can be either Draft, In Review, Returned, Approved or Posted.

Only when all timesheet lines on a timesheet are Approved will the status for all lines be updated to Posted.

Need to identify for a particular project, using a slicer to select ProjectName, what are all the timesheet lines on the same timesheet number where there is an Approved timesheet line for that particular project.

Data Table (Table Name: TSTable)

Timesheet Nbr	Timesheet Line Status	Proj ID	Proj Name	ProjTransDate	Hours	Emp ID	Emp Name
TS-0001	Approved	0001-M	Green	1/04/2024	4	E001	Jo Smith
TS-0001	In Review	0002-D	Yellow	2/04/2024	3	E001	Jo Smith
TS-0001	In Review	0003-M	Blue	3/04/2024	2	E001	Jo Smith
TS-0002	Approved	0001-M	Green	4/04/2024	5	P002	Jamie Walker
TS-0002	Returned	0002-D	Yellow	5/04/2024	8	P002	Jamie Walker
TS-0002	Approved	0004-K	Red	6/04/2024	6	P002	Jamie Walker
TD-0003	Posted	0001-M	Green	6/03/2024	4	E001	Jo Smith

[Ritaf1983]

Hi @Planet3000 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523