How to get all data on chart

[PPStar]

Hello. 

I have 2 tables. A Tickets Table and a Data Table. 

Date Table is related to the Tickets Table via a 1:M relationship with Date (From Date Table) and opened_at (From All Tickets Table)

I have the following data from my Tickets Table

I want to display a visual which shows me all the tickets created per month and all the tickets closed per month. 

On my chart, i have Month Year (from the date table) on the x axis and a Count of all the tickets opened_at and the count of all the tickets closed_at on the y axis, as below

However, i think the above visual is incorrect. If i click on the table above, for e.g if i click on a ticket that was opened on 29th Febuary and closed_at 8th March - should the graph higlight for Feb and March. At the moment, when i click on a ticket that was opened_at March and closed_at in Feb, the graph highlights just the March, as that was when the ticket was opened. 

If i click on the orange (closed_at) in April, i would hope to see all the tickets that we closed in April, however, the table visual just shows me all the ticket opened in April. 

I hope that makes sense.. 

I think this is related to my relationship.. 

Basically, i want to be able to show a visual to show all the tickets opened per month and all the tickets closed per month. 

[Anonymous]

Hi @PPStar 

This is about relation, you will have to create two relation from Date Table to Tickets Table.
When you create a relation then one will be active and another will be unactive. So, create active relation with opened_at and inactive relation with closed_at. 

Once you are done with modelling part. Then create measure, for closed_at using "Calculate" and "Userelationship" to make it active. 

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

[PPStar]

Thank you, i never knew this. I will try this shortly. Thanks again

[Slim-]

Hi,

Problem is coming from relation : the relation is between the opening date and the calendar table date.

Therefore when you use the calendar table date on x axis, the data is shown for the calendar table date -> opening date.

To counter this, keep your relationship between opened_at and your calendar table but also create another relationship between closed_at and the date from your calendar table. This second relationship won't be active since you can only have one relationship active : you can now create a measure using the function "CALCULATE" with "USERELATIONSHIP" that will override the relationship active to use the one you specify.

Regards

[PPStar]

Im getting a odd result. 

SO i have the followng relationship : 

Date 1:M to closed_at is Inactve. 

Date 1:M to opened_at is active

I created two measures : 

Closed_At Count = CALCULATE(COUNTA('All Tickets'[closed_at]), USERELATIONSHIP('Calendar'[Date],'All Tickets'[closed_at]))

Opened_at = CALCULATE(COUNTROWS('All Tickets'))

When i plot these visuals on my graph, 

Howver, if i clikc on Closed for March, it shows me Closed_at for April in the table below. (March 2024 is highligted) 

Are my measures wrong?

All i want is count of tickets opened and closed per month. 

Thanks

[Slim-]

@PPStar,

If you look at your graph in the original post, values were 46 opened and 43 closed in march.

After using USERLATIONSHIP, you have 46 opened and 42 closed.

I think your measures are good.

However, the columns in the table below do not seem to correspond to the graph (total 46 and 43 in table vs 46 and 42 in graph).

Try to plot in your table two columns : 'Calendar'[Date] and [Closed_At Count]. You should have the right numbers.

Let me know if not,

Regards

[PPStar · Accepted solution]

I have been playing around, and i thnk my measures were wrong. 

I fixed the measures and it appears to be working ok now. 

[PPStar]

Thank you very much. I will try this shortly.