March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi,
I wish to display the amount of hours in total my employees have worked in a month as well as display the different types of tasks they achieved in the same matrix visual from two different sources using a date table on a slicer.
My data in the EmployeeHoursTable looks like the following
My data in the TypeTable looks like the following
I have created a date table by the following and set the many to one relationship
Solved! Go to Solution.
Hi @PowerBiNoob37 ,
I updated your sample pbix file(see attachment), please check whether that is what you want.
1. Change the data type of field Date in table CalanderTable as Date
2. Delete the relationship between EmployeeHoursTable and CalanderTable
3. Create two measures as below to get the worked hours
Measure =
VAR _selyear =
SELECTEDVALUE ( 'CalanderTable'[Date].[Year] )
VAR _selmonth =
SELECTEDVALUE ( 'CalanderTable'[Date].[MonthNo] )
VAR _hours =
CALCULATE (
SUM ( 'EmployeeHoursTable'[Hours] ),
FILTER (
'EmployeeHoursTable',
YEAR ( 'EmployeeHoursTable'[DateWorked] ) = _selyear
&& MONTH ( 'EmployeeHoursTable'[DateWorked] ) = _selmonth
)
)
RETURN
IF (
ISINSCOPE ( 'CalanderTable'[Date].[Year] )
&& ISINSCOPE ( 'CalanderTable'[Date].[Month] )
&& ISINSCOPE ( 'TypeTable'[Type] ),
BLANK (),
_hours
)
Worked Hours =
SUMX (
GROUPBY (
'CalanderTable',
'CalanderTable'[Date].[Year],
'CalanderTable'[Date].[Month]
),
[Measure]
)
4. Toggle the option "Word wrap" off and adjust the width of Values fileld [Worked Hours] to make it invisible manually
Best Regards
Hello @PowerBiNoob37
You don't have similar dates in the Type and Employee table.
As you can see we've only 202104-202108 in the Type table but in the Employee table we've 202109-202111. Also, please change the date type as Date in Employee and Calendar table because in the Type table there is only Date not Date and time.
That doesnt help me sorry, now if i add them it doesnt add the hours like i want, im trying to produce exactly this but in a matrix visual i can sort via a date slice.
Month | int | nly | ety | Hours Worked |
Jan-21 | 2 | 300 | ||
Feb-21 | 1 | 1 | 200 | |
Mar-21 | 2 | 200 |
.
I cannot seem to add hours without it looking like
I want it too look like (MS Painted) I can change the values as this is just test data, the values wont line up exactly, so in my case the count types would be empty and the hours would be in it,
Hi @PowerBiNoob37 ,
I updated your sample pbix file(see attachment), please check whether that is what you want.
1. Change the data type of field Date in table CalanderTable as Date
2. Delete the relationship between EmployeeHoursTable and CalanderTable
3. Create two measures as below to get the worked hours
Measure =
VAR _selyear =
SELECTEDVALUE ( 'CalanderTable'[Date].[Year] )
VAR _selmonth =
SELECTEDVALUE ( 'CalanderTable'[Date].[MonthNo] )
VAR _hours =
CALCULATE (
SUM ( 'EmployeeHoursTable'[Hours] ),
FILTER (
'EmployeeHoursTable',
YEAR ( 'EmployeeHoursTable'[DateWorked] ) = _selyear
&& MONTH ( 'EmployeeHoursTable'[DateWorked] ) = _selmonth
)
)
RETURN
IF (
ISINSCOPE ( 'CalanderTable'[Date].[Year] )
&& ISINSCOPE ( 'CalanderTable'[Date].[Month] )
&& ISINSCOPE ( 'TypeTable'[Type] ),
BLANK (),
_hours
)
Worked Hours =
SUMX (
GROUPBY (
'CalanderTable',
'CalanderTable'[Date].[Year],
'CalanderTable'[Date].[Month]
),
[Measure]
)
4. Toggle the option "Word wrap" off and adjust the width of Values fileld [Worked Hours] to make it invisible manually
Best Regards
Thankyou yingyinr, thankyou very much.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
116 | |
85 | |
79 | |
64 | |
59 |
User | Count |
---|---|
140 | |
122 | |
105 | |
94 | |
90 |