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
sevenhills's avatar
sevenhills
Icon for Super User rankSuper User
3 years ago

1. Create the rollup table in DAX like you posted above.

 

2. Add column using this syntax 

"Max Exception in a day" = 
CALCULATE( MAX([Exception Count]), 
FILTER('Rollup Table', 'Rollup Table'[transactions_date] = EARLIER('Rollup Table'[transactions_date])))

Note: If you are using total, it will give sum of these.

 

3. (My preference) Create a measure 

"Max Exception" = 
var _sel = SELECTEDVALUE('Rollup Table'[transactions_date])

Max in a day = IF(
          HASONEVALUE('Rollup Table'[transactions_date]), 
          Maxx( 
              Filter(all('Rollup Table'), [transactions_date] = _sel), 
              'Rollup Table'[Exception Count])
          , Max('Rollup Table'[Exception Count])
      )