Create top X table visual

[icbd]

Good morning all,

I am registering faults on a machine, where I have start time, end time and description of all faults. I am trying to get a table visual to only display the longest duration fault for each. I'm getting duplicates and I'm not too sure I understand why, see image below.

I only want the max value per each description, which is not what I'm getting. Any help would be appreciated.

[BA_Pete · Accepted solution]

Hi @icbd ,

First create a measure to get your MAX duration:

_maxDuration = MAX(yourTable[Duration])

Then create a measure that picks out your [Fault Start] date where the duration is the MAX:

_maxFaultStart = 
VAR __maxStart =
MAX(yourTable[Duration])
RETURN
CALCULATE(
    SELECTEDVALUE(yourTable[Fault Start]),
    yourTable[Duration] = __maxStart
)

Put [Description], [_maxDuration], and [_maxFaultStart] into a table and it should do want you need.

Pete

[icbd]

Wouldn't this return only a single value? Not too sure this will achieve what I need.

[BA_Pete]

Hi @icbd ,

Yes, it will return a single value (the maximum), but when you add context (by including [Description] in the table) it will be evaluated only over the [Description] group thus giving you the maximum values per [Description], as per your requirement: "I only want the max value per each description".

Pete

[icbd]

Thank you, I was able to get this to work. Much appreciated.