DAX Formula IF ISBLANK with LOOKUPVALUE

[Anonymous]

Hi,

The following lookupvalue formula works however the output is not quite right i.e. the lookupvalue is not including ALL blank cells, just some. Eg. if lookup cell is blank, return "Rest of Network" however I found numerous blank cells in the data that match the criteria but were NOT returning "Rest of Network", instead it was returning "" (blank). 

Am I missing something?

=IF('Flat File Data'[OnTime]="No" && ISBLANK(LOOKUPVALUE(DelayedScans[Delay Point],DelayedScans[Item_Id],'Flat File Data'[Item_Id])),"Rest of Network",IF('Flat File Data'[OnTime]="Yes","On-Time",LOOKUPVALUE(DelayedScans[Delay Point],DelayedScans[Item_Id],'Flat File Data'[Item_Id])))

[harshnathani · Accepted solution]

Hi @Anonymous ,

Your code look okay, but try using BLANK().

Try this simplified code.

Column =
VAR _lookup =
    LOOKUPVALUE (
        DelayedScans[Delay Point],
        DelayedScans[Item_Id], 'Flat File Data'[Item_Id]
    )
RETURN
    SWITCH (
        TRUE (),
        'Flat File Data'[OnTime] = "No"
            && _lookup
                = BLANK (), "Rest of Network",
        'Flat File Data'[OnTime] = "Yes"
            || 'Flat File Data'[OnTime] = "On-Time", _lookup
    )

Also, hope the lookup value is returning a String.

A good video on Blank(), ISBLANK(), NULL and Empties.

https://www.youtube.com/watch?v=C26DQkb4hyY

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Anonymous]

Thank you so much, that worked perfectly...just had to remove "||"

Appreciate the help.

[sturlaws]

Hi @Anonymous 

you code looks ok, but without any sample data I can't help you understand why it does not work as intended.

Cheers,
Sturla