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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
byronp
Frequent Visitor

Showing total staff groups over a time period in a line graph.

Hi All, 

 

Strugging to find a solution for this problem i have.  I have two staff groups with just the start time and end time of their shifts. 

 

Example: 

UserIDGroupStartTimeEndTime
1A8:00:00 AM3:00:00 PM
2A9:00:00 AM4:00:00 PM
3A10:00:00 AM5:00:00 PM
4A9:30:00 AM3:00:00 PM
5B7:00:00 AM3:00:00 PM
6B9:00:00 AM3:00:00 PM
7B9:00:00 AM4:00:00 PM
8B9:30:00 AM5:00:00 PM

 

I want to display this in a multi line grpah with the sum total of the staff as the Y axis 

 

Example: 

byronp_0-1633907734993.png

 

 

How do i go about presenting this in a graph for my dashboard?

 

Any help would be greatly appreciated

 

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@byronp see the attached solution, basically, you need a table for hours that you will use on the x-axis and a measure to count staff, and then it is all easy.

 

Follow us on LinkedIn

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

9 REPLIES 9
parry2k
Super User
Super User

@byronp what makes you think it is not getting represented? What you are expecting?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

The reason behind that could be that lines are overlapping, switch to clustered column chat to check if you see the values for each group.

 

Also if you just reply saying I cannot see the result it doesn't help in anyways to provide a further solution. You have to be more specific and provide more detail.

 

Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

@byronp do you see any issue with the solution I provided. Is something not working?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Yes your solution makes sense, however i cannot recreate the results in my Dashboard. Im not getting the second group in the line graph at all. I have three groups in my results but even if i add a filter for only two of them i still do not get the two lines on the line graph. 

 

 

parry2k
Super User
Super User

@byronp No, you are not creating a relationship. You can always check in my attached file there is no relationship.

 

Follow us on LinkedIn

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

@byronp see the attached solution, basically, you need a table for hours that you will use on the x-axis and a measure to count staff, and then it is all easy.

 

Follow us on LinkedIn

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Thanks Parry2k, 

 

But quick question though. Would i need to have a relationship between the hours table and the original table, and if so what am i connecting the time table too?  do i connect it to the start time or the end time ?

TheoC
Super User
Super User

Hi @byronp 

 

The X-axis is currently is showing a "Time" - did you want the Start Time or End Time of the Shift?  Also, are you using a Time Table in your Dashboard?  If not, I'd recommend using one such as below:

TimeTable =

 

VAR HourTable = SELECTCOLUMNS(GENERATESERIES((0), (23)), "Hour", [Value])

VAR MinuteTable = SELECTCOLUMNS(GENERATESERIES((0), (59)), "Minute", [Value])

VAR SecondsTable = SELECTCOLUMNS(GENERATESERIES((0), (59)), "Second", [Value])

 RETURN

 

ADDCOLUMNS(

CROSSJOIN(HourTable, MinuteTable, SecondsTable),

"Time", TIME([Hour], [Minute], [Second])

)

From https://kohera.be/blog/power-bi/how-to-create-a-time-table-in-power-bi-in-a-few-simple-steps/

 

The last question prior to putting together a solution is your reference to a "Sum Total of Staff" - is this a Sum Total of Staff as individual persons (i.e. by their UserID) or the Staff Group level (i.e. by their Group A or B)?

 

Thanks heaps, 

Theo

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

byronp
Frequent Visitor

Thanks for the reply Theo, 

The graph is to show the number of staff that a present at the times of the day so it wouldnt be based on the start time or end time as one of the other. 

 

I would like to group it on the staff group to show how many of that staff group was present at any point in the day 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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