Forum Discussion

alagator28's avatar
alagator28
Helper II
3 years ago

Multiple x axes in same visual?

I'm new to Power Bi so please excuse the way I formulate my question as I might not use the correct terms.

 

I have built reporting based on support ticket data which includes fields like CreatedDate, ClosedDate, TicketNumber, etc. I am able to visualize them on graphs and perform calculations. So far so good. However, I've hit a bit of a bump in the road, as I'm trying to accomplish the following in one single visual:

 

  • Number of tickets created (by CreatedDate)
  • Number of tickets closed (by ClosedDate)

When building the visual, if I select the CreatedDate as the x-axis, it is also filtering the closed tickets by that date. If I use the ClosedDate as the x-axis, it is also filtering the created tickets by that date. I would basically like to combine the x-axis into "Month" which will show the tickets created in that month and the tickets closed in that month.

 

What is the best way to accomplish this? I would appreciate an example of a solution so I can work with something as I'm fairly inexperienced and it's tough for me to take something vague and run with it.

 

Thanks in advance!

Need Help

1 Reply

  • grantsamborn's avatar
    grantsamborn
    Solution Sage
    3 years ago

    Hi alagator28 

    If you are using a date table, you can create 2 relationships between your table and the date table.

    - Active 1:many relationship between Date[Date] and [Created] from your table.

    - Non-active 1:many relationship between Date[Date] and [Closed] from your table.

     

    Using USERELATIONSHIP, you can temporarily activate the 2nd relationship to calculate the number of closed.

    No. Created = COUNTROWS( 'Tickets' )

No. Closed = 
CALCULATE(
    COUNTROWS( 'Tickets' ),
    USERELATIONSHIP( 'Date'[Date], 'Tickets'[Closed] )
)

    pbix: https://drive.google.com/file/d/1IhJZN0tZkh1lOWz3gowZiXno30Y06DPQ/view?usp=share_link

     

    Let me know if you have any questions.