Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

count events in progress steps needed

Hello all, I have a fact table and a date table, what I am trying to do is count open tickets from previous months, count new tickets for the month and count tickets that did not close. I have experi...
Need Help
Tutorial Requests
  • TomMartens's avatar
    TomMartens
    8 years ago

    Hey,

     

    I guess that one of the articles you already have been reading is this one:

    http://sqljason.com/2012/11/classifying-and-solving-events-in.html

     

    Without sample data it's very difficult to say what's not working with your formula.

    I created a little PBI sample file and I adjusted your Formula to my data model and it seems to work, at least I get what I expected :-)

     

    I assume you created a measure in your fact table and used the relevant date columns from your unrelated date table.

     

    If my sample and the article from Jason Thomas does not help, I guess you have to create sample data, upload a file to onedrive or dropbox and share the link.

     

    Regards

    Tom

     

     

Anonymous's avatar
Anonymous
Not applicable
8 years ago

RemedyData

 

I think I need you email to share from one drive

TomMartens's avatar
TomMartens
Super User
8 years ago

Hey,

 

maybe you can't use onedrive because sharing content with external users (outside your organization) is disabled by your O365 admin, please check if you have this option available

 

Maybe you have to consider to use your personal onedrive or dropbox.

 

Regards

Tom 

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    I don't have those options. I will check the other methods.

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      I am making some progress, the two measures below return the same result, I am trying to count open tickets from previous months. I need to validate the results but I think I am heading in the right direction. I am using YearMonth like 2018/Jan from the fact table and Year Month Short from the date table, Is this acceptable?

       

      Edit to my original post: in the formula below is 2017/Dec recognized as "Less Than" 2018/Jan ? or do I need to use a different date format using month number?

       

      CarriedOver = CALCULATE(
        DISTINCTCOUNT('ODI Inovation Metrics'[Remedy ID]),
         FILTER( 'ODI Inovation Metrics', 'ODI Inovation Metrics'[StartYearMonth] < CALCULATE(MAX(Date_New[Year Month Short]))),
         FILTER( 'ODI Inovation Metrics', 'ODI Inovation Metrics'[EndYearMonth]  > CALCULATE(MAX( Date_New[Year Month Short]))))

       

      CarriedOverV2 = CALCULATE(
        DISTINCTCOUNT('ODI Inovation Metrics'[Remedy ID]),
         FILTER( 'ODI Inovation Metrics', 'ODI Inovation Metrics'[StartYearMonth] < MAX(Date_New[Year Month Short])),
         FILTER( 'ODI Inovation Metrics', 'ODI Inovation Metrics'[EndYearMonth] = BLANK() || 'ODI Inovation Metrics'[EndYearMonth] > MAX( Date_New[Year Month Short]))   
         )

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Tom, personal one drive requires an upgrade and fee and I can't get to DropBox from my state work station, It's blocked. Any other way I can share the .pbix. I can get it to dropbox or Google drive tonght at home.