Forum Discussion

muhammedaslam02's avatar
muhammedaslam02
Frequent Visitor
3 years ago
Solved

line and clustered chart

 

 

 

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

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    3 years ago

    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.

2 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    3 years ago

    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.  

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    3 years ago

    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.