Forum Discussion
Create top X table visual
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.
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
4 Replies
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
Wouldn't this return only a single value? Not too sure this will achieve what I need.
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
