Forum Discussion

sbuster's avatar
sbuster
Icon for Helper I rankHelper I
3 years ago

record with max value by day

I have a dataset below that has multiple records on a given day.  What I would like to do is find the AssetID with the max number (count) of ExceptionID by day....  I was able to use groupby a...
Need Help
johnt75's avatar
johnt75
Icon for Super User rankSuper User
3 years ago

You could try

Top exceptions by day =
GENERATE (
    VALUES ( 'Table'[Date] ),
    TOPN (
        1,
        CALCULATETABLE (
            GROUPBY (
                transactions,
                transactions[AssetID],
                "Exception Count", COUNTX ( CURRENTGROUP (), transactions[ExceptionID] )
            )
        ),
        "Exception Count"
    )
)
sbuster's avatar
sbuster
Icon for Helper I rankHelper I
3 years ago

This formula simply returns the same result as my rollup table shown above.  The next step is to produce 1 record per day, where that record should be the one with the max exception count.  So basically 1 record for every day, with the assetid and the exception count (which is the result of finding the max exception count).

  • sbuster's avatar
    sbuster
    Icon for Helper I rankHelper I
    3 years ago

    I was able to solve this problem by finding this solution.  Thanks to everyone for your suggestions.