Count only the type of the item that occurs at the earliest time

[katto16]

Hi guys.  So I have this matrix table with Unit IDs that have different failure types.
Failure A Count is a measure I created based on a calculated column [Failure A]

Failure A count = 
CALCULATE(
    DISTINCTCOUNT('Table'[Unit ID]),
    'Table'[Overall Result] = "fail" &&
    'Table'[Failure A]= 1
)

Failure B and Failure C are also measures I created based on original columns of the table.

Failure B = 
CALCULATE(
    DISTINCTCOUNT('Table'[Unit ID]),
    'Table'[Overall Result] = "fail" &&
    ('Table'[TL] >= 0.25
    || 'Table'[IL] >= 0.25
    ||'Table'[H] >= 0.25))

Failure C= 
CALCULATE(
    DISTINCTCOUNT('Table'[Unit ID]),
    'Table'[Overall Result] = "fail" &&
    'Table'[ZP] < 1) 

What I'm trying to achieve here is to "count the first failure type that occurs for a particular Unit ID"  As you can see here, some Unit IDs have multiple failure types but I only want to count the failure type that happens at the earliest time.

In this case, 2248022 is Failure C,
2248023 is Failure A, and 

2248024 is Failure B. 

Eventually, I want to get the total count of each failure types as a measure. They will be just like the measures I created Failure A Count, Failure B and Failure C but this time round, it will only consists of the failure types that happens at the earliest time.

So, I'm hoping it could be like either of these.

Thanks in advance! Appreciate for any help possible.

[v-rongtiep-msft · Accepted solution]

Hi @katto16 ,

Please have a try.

Create a measure.

Measure = VAR _1= CALCULATE(MIN('Table'[Time]),FILTER(ALL('Table'),'Table'[ID]=SELECTEDVALUE('Table'[ID])))
RETURN
IF(MAX('Table'[Time])=_1,MAX('Table'[subject]),BLANK())

Or a column.

column = VAR _1= CALCULATE(MIN('Table'[Time]),FILTER(ALL('Table'),'Table'[ID]=EARLIER('Table'[ID])))
RETURN
IF(('Table'[Time])=_1,('Table'[subject]),BLANK())

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[katto16]

Hi @v-rongtiep-msft 
The DAX code works well. Just one thing, how do I find the count of each of Failure A, B and C. 

And also, my original Failure B and Failure C are measures. I managed to turn the Failure B and C into columns so that this measure code of yours

Measure = VAR _1= CALCULATE(MIN('Table'[Time]),FILTER(ALL('Table'),'Table'[ID]=SELECTEDVALUE('Table'[ID])))
RETURN
IF(MAX('Table'[Time])=_1,MAX('Table'[subject]),BLANK())

will work. What if I want to keep Failure B and C as measures but make use of your measure code? It was giving me the error

"Column 'Failure B' in table 'Table' cannot be found or may not be used in this expression."

Just wondering. Thanks for the help.