DAX: Combinding lookupvalue with first non blank

[adam_mac]

Hi, i created a calculated column that returns the latest activity type by date. The issue that i am having is that sometimes there are multiple activity types on the same date. When this happens the lookupvalue formula returns nothing. 

What i would like the formula to do is to return the first or last activity (doesnt matter which) just so that something is being populated. 

Latest Activity Date2 = 

VAR Current_ID = AccountAddressCollection[Account.UUID]

VAR Max_Date = CALCULATE(MAX(MergedActivities[StartDateTime]), FILTER(MergedActivities,MergedActivities[AccountUUID]= Current_ID))

RETURN

LOOKUPVALUE(MergedActivities[Type],MergedActivities[AccountUUID],AccountAddressCollection[Account.UUID],MergedActivities[StartDateTime], Max_Date,"")

[selimovd · Accepted solution]

Hey @adam_mac ,

you could as alternate result for the LOOKUPVALUE function (last argument) return the MAX or MIN Type for that combination with the following CALCULATE:

CALCULATE(
    MAX( MergedActivities[Type] ),
    MergedActivities[StartDateTime] = Max_Date && MergedActivities[Account.UUID] = Current_ID
)

So the measure could look like this:

Latest Activity Date2 =
VAR Current_ID = AccountAddressCollection[Account.UUID]
VAR Max_Date =
    CALCULATE(
        MAX( MergedActivities[StartDateTime] ),
        FILTER(
            MergedActivities,
            MergedActivities[AccountUUID] = Current_ID
        )
    )
VAR Alternate_Result =
    CALCULATE(
        MAX( MergedActivities[Type] ),
        MergedActivities[StartDateTime] = Max_Date
            && MergedActivities[Account.UUID] = Current_ID
    )
RETURN
    LOOKUPVALUE(
        MergedActivities[Type],
        MergedActivities[AccountUUID], AccountAddressCollection[Account.UUID],
        MergedActivities[StartDateTime], Max_Date,
        Alternate_Result
    )

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic