Filter or group elements of a table, but keep the info in Power BI Desktop

[denxx34]

Hello @ all ,

I have the following table and would like to filter or aggregate the table according to the MIN (DurationSec) criterion. As a result, the fastest route should come out per name (with station).

In this case, I don't really care whether M or DAX.

INPUT:

Name	Station.Name	Route.DistanceMeter	Route.DurationSec
Rumbo	South	3193	465
Rumbo	North	5415	748
Rumbo	Reha 2	8501	877
Rumbo	Middle	11277	981
Rumbo	Reha 1	7187	990
Rumbo	West	9719	1193
Toll	North	10203	728
Toll	Reha 1	10410	930
Toll	West	13273	996
Toll	Middle	2858	462
Toll	Reha 2	4215	764
Toll	South	8601	709

OUTPUT shoulb be:

Name	Station.Name	Route.DistanceMeter	Route.DurationSec
Rumbo	South	3193	465
Toll	Middle	2858	462

Can someone help me please.

It's not possible to use GroupBy or SUMMARIZE().

Peace.

[v-diye-msft · Accepted solution]

Hi @denxx34 

Let me know if you'd like to get below results:

Measure = var a = CALCULATE(MIN('Table'[Route.DurationSec]),ALLEXCEPT('Table','Table'[Name]))
Return
IF(MAX('Table'[Route.DurationSec])=a,a,BLANK())

 

Pbix attached.

[denxx34 · Accepted solution]

Problem resolved as follows:

0. "INPUT" (raw data)
1. Reference with Group by Name MIN(DurationSec) "InputBest"
2. INNER JOIN "INPUT" + "InputBest" over Name & DurationSec -> OUTPUT

See .pbix attachment

In my opinion, that's the solution.

P.S.

How can I offer the pbix file as a download? 😐

[v-diye-msft · Accepted solution]

Hi @denxx34 

Let me know if you'd like to get below results:

Measure = var a = CALCULATE(MIN('Table'[Route.DurationSec]),ALLEXCEPT('Table','Table'[Name]))
Return
IF(MAX('Table'[Route.DurationSec])=a,a,BLANK())

 

Pbix attached.

[Greg_Deckler]

Seems like you can use Name in a Table visualization and then use standard MIN for distance and duration. Then perhaps this for station: https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/td-p/985814

[denxx34]

Hi @Greg_Deckler ,

Thanks for your quick reply.
However, I do not need a measure, but a solution for the whole table, which I then reuse in other scenario.

Do you have another idea?

Thanks.