Forum Discussion
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]))
)
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
11 Replies
Upload your file to onedrive and share the link.
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
