Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
rixmcx59
Helper V
Helper V

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 experimented with some of the solutions but not getting the right result. Some detail would help, like do I add a measure or a column, and to which table? I tried the formla below as a measure and a column but the numbers are too high when used in a matrix table. The date table is not related as recommended in other posts

OpenTickets =
 CALCULATE(
  DISTINCTCOUNT('Inovation Metrics'[RemID]),
   FILTER( 'Inovation Metrics', 'Inovation Metrics'[DateKeyStart] <= MAX(Date_New[Date Key])),
   FILTER( 'Inovation Metrics', 'Inovation Metrics'[DateKeyClosed] = BLANK() || 'Inovation Metrics'[DateKeyClosed] > MAX( Date_New[Date Key]))   
   )

 

1 ACCEPTED SOLUTION
TomMartens
Super User
Super User

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

 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

11 REPLIES 11
TomMartens
Super User
Super User

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

 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Tom, the link you provided led me to the answer.

Anonymous
Not applicable

HI @rixmcx59

 

Could you please post some sample data and expected result as well?

 

Thanks
Raj

Yes I looked at the article and many more. I am fairly new at this and can't really grasp a starting point.

 

What is the best way to share my.pbix? SharePoint Online One Drive?

It has 8084 rows

Upload your file to onedrive and share the link.



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

RemedyData

 

I think I need you email to share from one drive

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

image.png

 

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

 

Regards

Tom 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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.

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

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]))   
   )

Thanks for the reply, the table below is counting new tickets submitted, I need to add two measures, I need to count tickets carried over from previous months and tickets that did not close during the month. I have a Submitted date and a closed date in the table.

RemedyMatrix.png

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors