Extract the whole line of a table containing the maximum value

[Toine40]

Hi everyone, 
I have this table, in which each line represents a stop cause and the duration of each stop :

I manage to create a table to sum the stop duration and group by stop cause with this formula : 

Tab_Cause_Arret_maxi =

SUMMARIZECOLUMNS

(

'Arrêts'[Date],'Arrêts'[Libellé Motif],

FILTER('Arrêts','Arrêts'[Type d'arrêt] IN {"ARE", "AI", "ANQ"} && 'Arrêts'[Atelier]="U2_M_DECOUPE"),

"Tps_maxi",SUM('Arrêts'[Temps (en min)])

)
Here's the result :

What I would like now is to get only the first line, corresponding to the maximum stop cause 
I manage to get the max stop duration with this formula : 

CAUSE MAX = MAXX(Tab_Cause_Arret_maxi,Tab_Cause_Arret_maxi[Tps_maxi])

But what I want is the whole line, sth like this, with the stopo cause and the duration :

Can someone help ?
And maybe there's a way to get the result without creating this new table ?

Thanks to all of you

[Anonymous]

Hi @Toine40 

Please try this measure:

MEASURE =
CONCATENATEX (
    TOPN ( 1, 'Tab_Cause_Arret_maxi', 'Tab_Cause_Arret_maxi'[Tps_maxi], 0 ),
    'Tab_Cause_Arret_maxi'[Libellé Motif] & " " & 'Tab_Cause_Arret_maxi'[Tps_maxi]
)

the result is as follow:

Best Regards

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

[Toine40]

Thks a lot v-zhengdxu-msft, 

This works fine !!! I'd like we could mix both your answer and the one of DataNinja777 to avoid the creation of a new table, but if we can't you solution is th best. Thanks a lot for this

[Anonymous · Accepted solution]

Hi @Toine40 

Please try this measure:

MEASURE =
VAR _Tab_Cause_Arret_maxi =
    SUMMARIZECOLUMNS (
        'Arrêts'[Date],
        'Arrêts'[Libellé Motif],
        FILTER (
            'Arrêts',
            'Arrêts'[Type d'arrêt]
                IN { "ARE", "AI", "ANQ" }
                    && 'Arrêts'[Atelier] = "U2_M_DECOUPE"
        ),
        "Tps_maxi", SUM ( 'Arrêts'[Temps (en min)] )
    )
RETURN
    CONCATENATEX (
        TOPN ( 1, _Tab_Cause_Arret_maxi, [Tps_maxi], 0 ),
        [Libellé Motif] & " " & [Tps_maxi]
    )

This measure creates a virtual table variable, so that you don't need to create an entity table anymore.

Best Regards

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

[Toine40]

This is it !!!! Exactly ! Thank you so much @v-zhengdxu-msft 🙏🙏

[DataNinja777]

Hi @Toine40 ,

To get the maximum stop cause and its corresponding duration, you can achieve this either by creating a dynamic measure or by defining a calculated table, depending on your needs.

For a dynamic solution without creating a new table, you can write a measure that identifies the maximum stop duration and retrieves the associated stop cause. The measure can be defined as follows:

Max_Stop_Cause = 
VAR MaxDuration = 
    MAXX(
        FILTER(
            'Arrêts',
            'Arrêts'[Type d'arrêt] IN {"ARE", "AI", "ANQ"} &&
            'Arrêts'[Atelier] = "U2_M_DECOUPE"
        ),
        'Arrêts'[Temps (en min)]
    )
VAR MaxRow =
    TOPN(
        1,
        FILTER(
            'Arrêts',
            'Arrêts'[Type d'arrêt] IN {"ARE", "AI", "ANQ"} &&
            'Arrêts'[Atelier] = "U2_M_DECOUPE"
        ),
        'Arrêts'[Temps (en min)],
        DESC
    )
RETURN
    CONCATENATEX(
        MaxRow,
        'Arrêts'[Libellé Motif] & " - " & MaxDuration,
        ", "
    )

This measure dynamically calculates the maximum stop duration by filtering the relevant rows and creates a string combining the stop cause and the maximum duration. 

Best regards,

[Toine40]

Hi @DataNinja777,
This looks so great, that's exactly what I need ! Only thing is the result is

This is the second max cause and not the first one ; I've been trying to understand for more thant 1 hour but can't find the reason