line and clustered chart

[muhammedaslam02]

I have a line and clustered column chart.
Can anyone help me as I want to add the below values to the line y axis
1)count of SLA Vilotaed per month
2)Count of "user1" per month

https://www.dropbox.com/transfer/cap_pid_ft:AAAAAB7o9L_Wqu7WvNnrZSWbJumuObCTE3L8mTC16n3_7IXsl3ey6_Y

[v-jingzhang · Accepted solution]

Hi @muhammedaslam02 

Here are my measures. Hope they would be helpful. 

User1 Count =
CALCULATE (
    COUNT ( Tickets[Ticket ID] ),
    FILTER (
        Tickets,
        Tickets[Created time] <= MAX ( Dim_Date[Date] )
            && (
                Tickets[Closed time] >= MIN ( Dim_Date[Date] )
                    || ISBLANK ( Tickets[Closed time] )
            )
            && Tickets[User] = "USER1"
    )
)

SLA Violated Count = 
CALCULATE (
    COUNT ( Tickets[Ticket ID] ),
    FILTER (
        Tickets,
        Tickets[Created time] <= MAX ( Dim_Date[Date] )
            && (
                Tickets[Closed time] >= MIN ( Dim_Date[Date] )
                    || ISBLANK ( Tickets[Closed time] )
            )
            && Tickets[Resolution status] = "SLA Violated"
    )
)

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Ashish_Mathur]

Hi,

There should first of all be an active relationship between the Created date or the Closed date column to the Date column of the Calendar Table.  But even before that, you should create 2 additional columns - Create date and Closed date.  We will also have to create an inactive relationship.  Only after this basic set up is done should we even think of answering your questions.