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])))

[yaya1974]

I am having a similar issue.  My lookupvalue formula is pulling in the Avg price of year, model, finish and market.  it works for every model but one. hmmmmmmmmmmmmm   Does anyone have any idea why and what I can do to fix?

Here is my code:

Avg Price = LOOKUPVALUE(Costs[Weighted OE/OES Price],Costs[AOP Year],BumperOEM[Year],Costs[Model],BumperOEM[Model],Costs[Finish],BumperOEM[Finish],Costs[PL Market],BumperOEM[PL Market])

I have checked all my raw data and it is fine. I have checked the costs table and prices are there with all above filters.  

I have no idea what is causing this.

Thank you!

[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