Returning only the MAX filtered value

[lukegilligan]

I am trying to create a straight table that only returns the value of a row that is the latest date for the event. I would normally do this in the load script, but this instance needs to be done within the dash.

I have read up that FIRSTSORTEDVALUE should be able to do this, however I also want the event effective dates to be limited to not show past the variable vDateEnd that is driven by some selectable buttons.

For instance in my attached demo, if the date selector is set to 2018/Q2 and Asset Code 50652 is selected, only Event ID 108198.0001 | TEN should be the value returned as it is the highest value that doesn't go past the vDateEnd of 31/12/2018.

Any help would be very much appreciated.

Thanks
Luke

[sunny_talwar]

Try this expression

=If(Max({<CRMSVAssetEvent.effectiveDate = {">=$(vDateStart)<=$(vDateEnd)"}>}TOTAL <CRMSVAsset.Code> CRMSVAssetEvent.effectiveDate) = CRMSVAssetEvent.effectiveDate,
	IF(CRMSVAssetEvent.CustodianOrg.Code LIKE '*VUT*','UT','TEN'))

[lukegilligan]

Thanks Sunny, that has kind of worked for what I need.

How would you recommend doing it if I needed to use more than 1 expression in the table? I tried using the same formula with different fields and it works, but I can't filter out the blank rows.

Is it possible to do something like this in the dimension instead of the expression?

[sunny_talwar]

If you want to use a calculated dimension, you can try this

Dimensions

CRMSVAsset.Code

=Aggr(If(Max({<CRMSVAssetEvent.effectiveDate = {">=$(vDateStart)<=$(vDateEnd)"}>}TOTAL <CRMSVAsset.Code> CRMSVAssetEvent.effectiveDate) = CRMSVAssetEvent.effectiveDate, CRMSVAssetEvent.effectiveDate), CRMSVAssetEvent.effectiveDate, CRMSVAsset.Code)

CRMSVAssetEvent.eventID

Expression

=IF(CRMSVAssetEvent.CustodianOrg.Code LIKE '*VUT*','UT','TEN')

and make sure to select 'Suppress When Value Is Null' for the second dimension