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.

Answer 2

1. Right-click the table choose New quick measure
2. In Calculation drop-down select Max per category
3. In Base value field, drag the Count column. In this, the value will be aggregated to Sum by default so instead change it to Max of Count
4. In Category field, drag the route column

Voila! Magic happens! The measure that is created, when plotted against the axis Route will give Max(Count) per Route. Here is what the DAX will look like:

Count max per route = 
MAXX(
    KEEPFILTERS(VALUES('Table1'[route])),
    CALCULATE(MAX('Table1'[Count]))
)

(so one can directly use this DAX without wanting to drag but i dont understand the DAX at this moment tbh)

Lucky reference for me: https://docs.microsoft.com/en-us/power-bi/desktop-quick-measures

Answer 3

Create a calculated column with formula:

MAX = IF(CALCULATE(
        MAX(Table1[Count]);
        FILTER(
            Table1;
            Table1[Date] = EARLIER(Table1[Date])
        )
    ) = Table1[Count]; Table1[Route]; BLANK())

Create your table and make a page level filter to show all non-blank values of Table1[MAX].