How to remove duplicates from a variable?

[UsePowerBI]

Hello

I have a MAX calculation stored in a variable:

VAR a = 

CALCULATE (

MAX ( 'Table1'[Column1] ),

'Table2'[Column2] = EARLIER ( Table1[Column3] )

RETURN

LOOKUPVALUE (

'Table1'[Column4],

'Table1'[Column5], Table2[Column3],

'Table1'[Column1].[Date], a

)

Because there are duplicates, it seems that this VAR stores a list of duplicate values instead of one value and I get the error of 'table returned when expected single value'.

How can I overcome this?

Thanks

[amitchandak]

@UsePowerBI ,The information you have provided is not making the problem clear to me. Can you please explain with an example.
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Appreciate your Kudos.

[AlB]

Hi @UsePowerBI 

What do you mean it stores duplicate values? MAX( ) is a scalar function and will thus return one value only. That is what you 're assigning to the variable (assuming the the code is missing a closing parenthesis right before the RETURN)

LOOKUPVALUE is also a scalar function and will return only one value. If multiple rows match the search values and in all cases Result_Column values are identical then that value is returned. However, if Result_Column returns different values an error is returned. Perhaps that is what's happening

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers