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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
EraseEgo
Frequent Visitor

X axis interval length

I am plotting the TSA Checkpoint throughput data available at https://www.tsa.gov/coronavirus/passenger-throughput using Power BI. I have restricted the data from March 1st until the latest available date and the X axis has the date values. Chart as it looks now is at https://imgur.com/a/mbYBWrF
 

Since it is showing each date on the X axis it is not able to display the entire chart without scrolling. Is there a way to change the X axis spacing to say 2 days instead of 1 day? X Axis has Categorical Type.

1 ACCEPTED SOLUTION
mahoneypat
Employee
Employee

You can add columns to your table to get week, month, etc.  Here is a quick way to get approximately half the number of dates in a calculated column (use your Date column instead of what is shown), and when you use it on the X axis, use a SUM( ) measure (so both rows with that "date" are added)..

 

OddDays = If(ISODD(Day('Date'[Date])), 'Date'[Date], 'Date'[Date]-1)
 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

5 REPLIES 5
nahomzw
Frequent Visitor

Have you got the solution for this ever? It looks simple but so tricky to get exact values.

mahoneypat
Employee
Employee

You can add columns to your table to get week, month, etc.  Here is a quick way to get approximately half the number of dates in a calculated column (use your Date column instead of what is shown), and when you use it on the X axis, use a SUM( ) measure (so both rows with that "date" are added)..

 

OddDays = If(ISODD(Day('Date'[Date])), 'Date'[Date], 'Date'[Date]-1)
 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Suggested solution is not exact since it causes averaging of values.

it's not averaging, it's aggregating. I think it's a wholly acceptable solution. The ask was to show more of your data in the visual.  You can either have day level granularity and only plot a small part of the data, or you can have week level granularity and show 7x as much etc.

 

You could consider adding a date hierarchy so that the aggregations happen automatically, and you still have the option to drill down in the hierarchy as needed.

It is aggregation. The type of aggregation by default is Sum that makes no sense since I don't want the values for odd and even days to be summed. Instead I used averaging which makes better sense, however even that is incorrect. Ideally, I am looking for a way that will just label the X-axis with odd dates or even dates or in general dates spaced by some fixed value.  This feature is available in most charting packages, not sure why Power BI does not have it. 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.