topic Re: Extract the whole line of a table containing the maximum value in DAX Commands and Tips

Extract the whole line of a table containing the maximum value

Toine40 — Sat, 14 Dec 2024 11:00:38 GMT

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

Re: Extract the whole line of a table containing the maximum value

DataNinja777 — Sat, 14 Dec 2024 11:56:04 GMT

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,

Re: Extract the whole line of a table containing the maximum value

Toine40 — Sat, 14 Dec 2024 14:09:36 GMT

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

Re: Extract the whole line of a table containing the maximum value

Anonymous — Mon, 16 Dec 2024 03:20:02 GMT

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.

Re: Extract the whole line of a table containing the maximum value

Toine40 — Mon, 16 Dec 2024 21:16:06 GMT

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

Re: Extract the whole line of a table containing the maximum value

Anonymous — Tue, 17 Dec 2024 02:19:58 GMT

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.

Re: Extract the whole line of a table containing the maximum value

Toine40 — Wed, 18 Dec 2024 07:53:22 GMT

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