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

Regular Visitor

Combine Line Charts Built off the Same Field

We have a simple table with visitor data that includes an arrival date/time and departure date/time, and we've charted the year's worth of data based on the hour of day, as shown in the attached image. This shows average arrivals per hour and average departures per hour. The average comes from a measure: Average Visitors = [Total Visitors]/[No. of Days], where Total Visitors is a record count, and No. of Days = DISTINCTCOUNT(Visitors[Arrival Date]). Each chart uses the same Average Visitors field. Is it possible to get these two lines on one chart? Or even just one line that showed the net by hour? If the latter, my guess is we'll have to add a measure to calculate that, but we are struggling with replicating what PowerBI is doing automatically in each chart.

4 REPLIES 4
Community Support

Hi @Ryan19 ,

It is suggested to create another Dates table. And then create relationship between your fact table and the Dates table. Use the date/time hour column from Dates table as x-axis.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Super User

Hi,

Share the link from where i can download your PBI file.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Super User

@Ryan19 , create a datetime table or create a date and time table (separately) and join that with arrival and departure. One will active another will be inactive. Activate other using userelation.

Time table

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

userelation

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...

Appreciate your Kudos.

Regular Visitor

Many thanks for this response. I'm still working through it all and trying to make it work, and I appreciate you taking the time to post all of this information.

Helpful resources

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - August 2024

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

Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors