topic DAX Formula IF ISBLANK with LOOKUPVALUE in DAX Commands and Tips

DAX Formula IF ISBLANK with LOOKUPVALUE

Anonymous — Tue, 23 Jun 2020 07:09:33 GMT

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

Re: DAX Formula IF ISBLANK with LOOKUPVALUE

sturlaws — Tue, 23 Jun 2020 07:39:43 GMT

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

Re: DAX Formula IF ISBLANK with LOOKUPVALUE

Anonymous — Tue, 23 Jun 2020 09:12:18 GMT

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)

Re: DAX Formula IF ISBLANK with LOOKUPVALUE

Anonymous — Tue, 23 Jun 2020 10:18:06 GMT

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

Appreciate the help.

Re: DAX Formula IF ISBLANK with LOOKUPVALUE

yaya1974 — Wed, 25 Sep 2024 16:28:03 GMT

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!