topic Re: If is blank in DAX Commands and Tips

If is blank

netanel — Wed, 02 Mar 2022 14:54:11 GMT

Hey All!

Need your help guys

i have this Measure:

_Site =

VAR _diff_Site =

CALCULATE (

MAX ( Append2[Finance Site] ),

FILTER (

Append2,

Append2[First Name] = EARLIER ( Append2[First Name] )

&& Append2[Person Number] = EARLIER ( Append2[Person Number] )

)

RETURN

IF ( _diff_Site <> Append2[Finance Site], "change", BLANK () )

The Measure basically tells me that if a person has made a change from record to record
I will get the word "change"
I want to add to it the possibility that even if person did not exist before
(I mean in the record before him)
So show me as "NEW"
mean that new employee who has joined

Can help?

Re: If is blank

amitchandak — Wed, 02 Mar 2022 15:54:50 GMT

netanel , I think you should record other then current that logic is missing.

may be date < earlier(date)

Return can be

Switch(True(),

isblank(_diff_Site), "New"

_diff_Site<> Append2[Finance Site], "change"

, BLANK () )

Re: If is blank

Anonymous — Mon, 07 Mar 2022 08:41:26 GMT

Hi netanel ，

Not sure if your data table has a date column like amitchandak mentions or a column with numeric data type for easy identification of each record, if it does, change your calculated column formula just as amitchandak suggested. Instead, you can add an index column in the Query Editor and update your calculated column formula as follows:

_Site = VAR _diff_Site = CALCULATE ( MAX ( Append2[Finance Site] ), FILTER ( ALL ( Append2 ), Append2[First Name] = EARLIER ( Append2[First Name] ) && Append2[Person Number] = EARLIER ( Append2[Person Number] ) && 'Append2'[Index] < EARLIER ( 'Append2'[Index] ) ) ) RETURN IF ( ISBLANK ( _diff_Site ), "New", IF ( _diff_Site <> Append2[Finance Site], "change", BLANK () ) )

Best Regards