Find Max for each day in PowerBI and DAX

Question

I am trying to find the maximum route for each day based on the count of cars in PowerBI/DAX. An example of my data is as follows:

 Date         Route      Count
 01-Nov-17     A           10
 01-Nov-17     B           5
 02-Nov-17     A           2
 02-Nov-17     C           22
 03-Nov-17     B           2

Hence I want to find the max count of route for each date and display the results of a table like so...

 Date         Route      Count
 01-Nov-17     A           10
 02-Nov-17     C           22
 03-Nov-17     B           2

Any suggestions would be very much appreciated.

Thanks, Fiona

Answer 1

First, define measure for max car count:

[Max Count] = MAX( Cars[Count] ) 

If you drop this measure into a pivot against dates, it will show you max car counts per date.

Define second measure:

[Max Routes] =
VAR Period_Max_Count = [Max Count]
RETURN
    CONCATENATEX (
        FILTER ( Cars, [Max Count] = Period_Max_Count ),
        Cars[Route], ","
    )

This measure will return a string of routes per date that have max count. You need a list instead of one value because of potential ties - multiple routes might have the same max count per period. It's not in your data example, but just to demonstrate this, I added an extra record for the first date:

The way this measure works: first, it saves max car count per date into a variable. second, it filters car table to select only routes that have count equal to max count for the date. third, it iterates over the filtered table and concatenates route names into a list separated by comma.