Forum Discussion
Helper IData slicing issue in Power BI
Hi All,
I am new to PowerBI. Can someone please help me to understand what I did wrong?
I have two SharePoint lists, one list has planned hours of staff and another one has the actual time of staff and both the list has project names. I have created many to many relationships using staff names. when I slice the data of any staff using staff name/project name. The Actual hours coming correctly but planned hours are not coming as expected. Attached is my relationship diagram
.
when I slice my data using the Staff name, I am getting the following results. which is wrong, since it is summing up my planned hours plus my team members planned hours together.
The desired output should be
Thanks in Advance.
Hi Arun_BI ,
Create a measure to replace [Claim Hours] in the visual.
Measure = CALCULATE(SUM('Time log'[Hours Claim]),FILTER('Time log','Time log'[Project Name] in VALUES(FTE[Project name])))Best Regards,
Jay
11 Replies
- amitchandak
Super User
Arun_BI , better to create a bridge table for staff name
Bridge Table: https://www.youtube.com/watch?v=Bkf35Roman8&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=19
https://www.seerinteractive.com/blog/join-many-many-power-bi/
Satff Name =
distinct(union(distinct(Table1[Satff Name]),distinct(Table2[Satff Name])))
Arun_BIHi amitchandak ,
Thanks for your reply. That's really nice to know about the bridge table. I have created a bridge table but still, the issue persists. I am not sure about the issue that I can only slice data from planned hours and not from actual hours. I am unable to attach my pbix file here so shared the screenshot of my relationship diagram. Is there a way that I can share my pbix file with you? Cheers
- amitchandak
Arun_BI , You can paste sample data from excel to here. Or upload sample data on dropbox or onedrive and share link
Do share expected output
- Arun_BI
Thanks amitchandak . I have copied my tables below.
FTE :
Staff name Project name FTE Allocated Start Date End date Weekly duration Project Duration Planned Hours Roelofs COKI 1.00 1 February 2022 31 December 2022 40 47 1880 Arun CIC PMO 0.20 1 February 2022 31 December 2022 8 47 376 Daniel BRII 0.50 1 February 2022 30 June 2022 20 21 420 Arun ASDAF 0.25 24 March 2022 28 July 2022 10 18 180 Arun COKI 0.25 28 July 2022 21 December 2022 10 21 210 Kathryn COKI 1.00 1 February 2022 31 August 2022 40 30 1200 Nancy BRII 0.80 17 February 2022 28 July 2022 32 23 736 Time log:
Staff Name Project Name Hours Claim Hours Claim date Category Roelofs COKI 10 6/01/2022 Meeting Roelofs COKI 10 26/01/2022 Meeting Arun CIC PMO 5 26/01/2022 Coding Kathryn COKI 1 6/01/2022 Meeting Arun ASDAF 15 27/01/2022 Coding Arun COKI 3 12/01/2022 Meeting Arun CIC PMO 4 26/01/2022 Meeting Arun CIC PMO 20 28/02/2022 Meeting Nancy BRII 10 17/02/2022 Meeting Nancy BRII 12 9/03/2022 Coding Daniel BRII 15 16/02/2022 Coding Roelofs COKI 10 27/01/2022 Coding The desired output will be:
When I select staff name and project name in the slicer both planned and actual hours should vary accordingly. Currently planned hours are varying and actual hours remain constant:
Hi Arun_BI ,
Create a measure to replace [Claim Hours] in the visual.
Measure = CALCULATE(SUM('Time log'[Hours Claim]),FILTER('Time log','Time log'[Project Name] in VALUES(FTE[Project name])))Best Regards,
Jay
- Arun_BI
Anonymous Thanks Mate, I am able to achieve my desired results by creating a measure. Thank you so much for the help and thanks to all the community members
- Ashish_Mathur
Hi,
Wouldn't you also want to have a slicer for selection of Month and Year. If yes, then which column of the FTE table should the Year/Month column slice? - Start Date or End Date or both? So if you select March 2022, what result do you expect to see for planned hours?
- Arun_BI
Hi Ashish_Mathur Thanks for showing interest in the post. You are right, I want to slice the data by Month & Year as well. I will use Timelog "Claim date". If I select March 2022, how many hours each staff entered on the month of March plus their planned hours will be displayed. Please let me know if you have any questions, i am happy to elaborate if needed.
