DAX: Combinding lookupvalue with first non blank

adam_mac — Mon, 26 Jul 2021 12:33:49 GMT

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,"")

Re: DAX: Combinding lookupvalue with first non blank

selimovd — Mon, 26 Jul 2021 12:50:25 GMT

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

topic DAX: Combinding lookupvalue with first non blank in DAX Commands and Tips

DAX: Combinding lookupvalue with first non blank

Re: DAX: Combinding lookupvalue with first non blank