Combine Line Charts Built off the Same Field

[Ryan19]

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.

[Icey]

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.

[Ashish_Mathur]

Hi,

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

[amitchandak]

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

[Ryan19]

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.