If is blank

[netanel]

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?

[Anonymous · Accepted solution]

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

[amitchandak]

@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 () )