Data slicing issue in Power BI

[Arun_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.

[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:

[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.

[Ashish_Mathur]

I believe you already have an answer to your question.

[Arun_BI]

Yes @Ashish_Mathur 

[v-jayw-msft · Accepted solution]

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]

@v-jayw-msft 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

[Arun_BI]

Thanks @v-jayw-msft I will try this out today and let you know my results. 

[amitchandak]

@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_BI]

Hi @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