Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
lakibalazs
New Member

Pivot for SLA per week

Hi,

 

I have a data source with daily entries for individuals, whether they come to office or not on the given day.

Each person is either billable or shadow, which can change with time. Besides date I have also have week# for convinience.

DateNameIn office?Week NumberShadow/Billable
2024-01-01AA BBFALSE1Billable
2024-01-02AA BBFALSE1Billable
2024-01-03AA BBTRUE1Billable
2024-01-04AA BBTRUE1Billable
2024-01-05AA BBFALSE1Billable
2024-01-06AA BBFALSE1Billable
2024-01-07AA BBFALSE2Billable
2024-01-08AA BBFALSE2Billable
2024-01-09AA BBFALSE2Billable
2024-01-10AA BBFALSE2Billable
2024-01-11AA BBTRUE2Billable
2024-01-12AA BBFALSE2Billable
2024-01-13AA BBFALSE2Billable
2024-01-14AA BBFALSE3Billable
2024-01-15AA BBFALSE3Billable
2024-01-16AA BBFALSE3Billable
2024-01-17AA BBTRUE3Billable
2024-01-18AA BBTRUE3Billable
2024-01-19AA BBFALSE3Billable
2024-01-20AA BBFALSE3Billable
2024-01-01AA CCFALSE1Billable
2024-01-02AA CCTRUE1Billable
2024-01-03AA CCTRUE1Billable
2024-01-04AA CCTRUE1Billable
2024-01-05AA CCFALSE1Billable
2024-01-06AA CCFALSE1Billable
2024-01-07AA CCFALSE2Billable
2024-01-08AA CCFALSE2Billable
2024-01-09AA CCFALSE2Billable
2024-01-10AA CCFALSE2Billable
2024-01-11AA CCFALSE2Billable
2024-01-12AA CCFALSE2Billable
2024-01-13AA CCFALSE2Billable
2024-01-14AA CCFALSE3Billable
2024-01-15AA CCFALSE3Billable
2024-01-16AA CCFALSE3Billable
2024-01-17AA CCFALSE3Billable
2024-01-18AA CCFALSE3Billable
2024-01-19AA CCFALSE3Billable
2024-01-20AA CCFALSE3Billable
2024-01-01AA DDFALSE1Shadow
2024-01-02AA DDFALSE1Shadow
2024-01-03AA DDTRUE1Shadow
2024-01-04AA DDFALSE1Shadow
2024-01-05AA DDFALSE1Shadow
2024-01-06AA DDFALSE1Shadow
2024-01-07AA DDFALSE2Shadow
2024-01-08AA DDFALSE2Shadow
2024-01-09AA DDFALSE2Shadow
2024-01-10AA DDFALSE2Shadow
2024-01-11AA DDFALSE2Shadow
2024-01-12AA DDFALSE2Shadow
2024-01-13AA DDFALSE2Shadow
2024-01-14AA DDFALSE3Shadow
2024-01-15AA DDFALSE3Shadow
2024-01-16AA DDTRUE3Billable
2024-01-17AA DDTRUE3Billable
2024-01-18AA DDFALSE3Billable
2024-01-19AA DDFALSE3Billable
2024-01-20AA DDFALSE3Billable

 

From the above table I'd like to create a pivot, where I need to count the number of individuals who were in the office equal or more than two times. It is a kind of SLA. I need to devide the individuals into Billable/Shadow categories and report separately:

 Week123
BillableInOffice>=2202
 InOffice<=1021
ShadowInOffice>=2000
 InOffice<=1110

 

Please help me in finding a good solution! Thank you!

1 ACCEPTED SOLUTION
ryan_mayu
Super User
Super User

@lakibalazs

you can try to create a column

 

InOffice =
VAR _count=countx(FILTER('Table','Table'[Week Number]=EARLIER('Table'[Week Number])&&'Table'[Shadow/Billable]=EARLIER('Table'[Shadow/Billable])&&'Table'[Name]=EARLIER('Table'[Name])&&'Table'[In office?]=true()),'Table'[Date])
return if(_count>=2,"InOffice>=2","InOffice<=1")
 
11.PNG
 
then create a matrix
12.PNG
 
pls see the attachment below
 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
lakibalazs
New Member

Thank you! Perfect Solution!

you are welcome





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




ryan_mayu
Super User
Super User

@lakibalazs

you can try to create a column

 

InOffice =
VAR _count=countx(FILTER('Table','Table'[Week Number]=EARLIER('Table'[Week Number])&&'Table'[Shadow/Billable]=EARLIER('Table'[Shadow/Billable])&&'Table'[Name]=EARLIER('Table'[Name])&&'Table'[In office?]=true()),'Table'[Date])
return if(_count>=2,"InOffice>=2","InOffice<=1")
 
11.PNG
 
then create a matrix
12.PNG
 
pls see the attachment below
 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.