Forum Discussion

asah's avatar
asah
Helper I
6 years ago
Solved

Help needed with Recursive / Iterative Calculation

Hi, I am requesting for some help in achieving the following outcome in Power BI which requires a recursive calculation. The source data has the following columns:   Date Ticket Created - The date...
Need Help
tkirilov's avatar
tkirilov
Resolver I
6 years ago

Hi asah ,

 

Perhaps the following measure: 

OpenTicketCount = Calculate(Distinctcount([Ticket Id]), [IsBreached] = "True")

 

And if you slice that by date in a table visual for example, it should give you the desired result. That's if I've fully understood your requirement of course.

ImkeF's avatar
ImkeF
Community Champion
6 years ago

Hi asah  

not sure about your requirement here. With regards to the first row: Do you want to count this item as open at the date of 31. January of example? So basically create a history of open/closed status?

  • asah's avatar
    asah
    Helper I
    6 years ago

    Hi ImkeF 

    Thanks for looking into this. Apologies, I noticed that I did not sort the sample data before uploading. It should be sorted first on the created date and then the ticket id. Regardless of that, this is what I am trying to achieve in a column:

    1) Considering that ticket created on 30-Jan is the first ticket in the source data, the backlog of open tickets out of SLA will be 0

    2) 2nd row suggests that a ticket has been created on 4-Feb, however the previous ticket has been closed on 3-Feb, so the backlog of open tickets out of SLA remain 0.

    3) Again, there is another ticket opened on 4-Feb and the previous ticket still remains open and IsSLABreach = False, so the backlog still remains 0. However, if the IsSLABreach had been True, the value would have been 1.

    So on and so forth. Please let me know if I am making sense or I will try and explain differently. Any help is greatly appreciated.

    Cheers,

    Anirudh

    • ImkeF's avatar
      ImkeF
      Community Champion
      6 years ago

      Hi asah ,

      so you're looking for a column on the existing table or a measure in your model?

      Am I assuming correctly, that all this has to be applied on Ticket-ID-Level (as row 4 starts again with 29th Jan..)?

       

      • asah's avatar
        asah
        Helper I
        6 years ago

        Hi Imke,

        I am hoping to produce a column as I'd like to display the values as a part of a matrix with other columns. The ticket IDs are incremental in nature so ideally should be by date created and then the ticket id's as multiple tickets can be created on the same day. My sample data was not sorted so may be causing the confusion.