Summarize a table keeping only first occurrences in timeline

[danilopbr]

Hello,

I would like to show WHEN and HOW MANY equipment failures occur in timeline. Then I use the following code:

CALCULATE(

     COUNTROWS(

          GROUPBY(
               'IssueTable',
               'IssueTable'[IssueID],
               'IssueTable'[Plant],
               'IssueTable'[Equipment]
          )
     )
)

My IssueTable have multiple rows for every issue reported but most rows are only updates of a single one issue. When I put these data in a timeline chart (bar graph), each update counts as a NEW issue (thats the problem!).

IssueTable

Date	IssueID	Plant	Equipment	Remarks (free text)
01/jan/21	1	Alpha	Turbine	Initial issue reporting
02/jan/21	1	Alpha	Turbine	Update1
03/jan/21	1	Alpha	Turbine	Update2
02/jan/21	2	Alpha	Turbine	Initial issue reporting
03/jan/21	2	Alpha	Turbine	Update1
04/jan/21	2	Alpha	Turbine	Update2
07/jan/21	1	Beta	Generator	Initial issue reporting
08/jan/21	1	Beta	Generator	Update1
09/jan/21	1	Beta	Generator	Update2

This is what I get:

What I wanted:

I appreciate any help.

[aj1973]

Hi @danilopbr 
Is this good?

[danilopbr]

Hello Amine Jerbi.

In fact this column (Remarks) is a free text one. That's why it has random content.

Do you know how to solve it?

Thank you for your collaboration.

[aj1973]

What is the source? Data in that column(Remarks) how is it inserted?

Basicaly you need a column where you have a standard input of the remarks, do you have one like it?

[danilopbr]

Data in that column(Remarks) how is it inserted?

Answer: it is typed with a description of the failure in a free style. There is no standard for filling this column.

Basicaly you need a column where you have a standard input of the remarks, do you have one like it?
Answer: I don't. I tried in many ways to find a code that summarizes a table considering only the first record (based on date column), because [Remarks] column is a free text (no standard).

[aj1973]

I don't know how to help you on this other than add a column where you make it look like standard data in it according to what you see in the Column Remarks, But this won't be automatic if the user enters something different that it already exist.

[danilopbr]

Thank you so much Amine.

[v-kelly-msft · Accepted solution]

Hi  @danilopbr ,

First go to query editor>add an index column;

Then create a measure as below:

Measure = 
var _minindex=CALCULATE(MIN('Table'[Index]),FILTER(ALL('Table'),'Table'[IssueID]=MAX('Table'[IssueID])&&'Table'[Plant]=MAX('Table'[Plant])))
Return
IF(MAX('Table'[Index])=_minindex,1,BLANK())

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[danilopbr]

Sorry for this delayed feedback.

Your suggestion was simple and brilliant.
Just a question: you used the calculate modifier below to group the table events, right? I would never have thought like this 😄

'Table'[IssueID] = MAX('Table'[IssueID]) && 'Table'[Plant] = MAX('Table'[Plant])

[aj1973]

Welcome