Setting Default DAX Value If Lookup Is Blank

[kyleb350]

I am creating a "Shipping ETA" DAX column on an Orders table. This table is linked to a Ship Time table by warehouse code.

In this shipping time lookup table, I want to have a default value if the warehouse code is not listed in the table, so I have the following table fields and sample values:

Orders  

Order #, Warehouse, Ship Date, Shipping ETA

1234, AAA, 1/1/2021, 1/8/2021

4567, BBB, 1/1/2021, 1/15/2021

7890, CCC, 1/1/2021, 1/22/2021

Ship Time 

Warehouse, Shipping Days

AAA, 7

BBB, 14

DEFAULT, 21

The reason for this table is that there are many other warehouses but I want to override the shipping times for these two warehouses while not hardcoding the shipping time within the report.

I know this syntax is incorrect, but hopefully it gives an idea what I'm shooting for:

Shipping ETA = if(isblank(related(Ship Time.Warehouse)),Ship Time.Warehouse = "DEFAULT",)
Ship Date + related(Shipping Days)

[parry2k · Accepted solution]

@kyleb350 if I understood correct, you want to add 21 days (default # of days) when warehouse not found in ShipTime table, if this is correct understanding then try this:

Shipping ETA = if(isblank(related(Ship Time.Warehouse)),LOOKUPVALUE ( Ship Time.[Shipping Days], Ship Time[Warehouse], "Default" ),
 related(Shipping Days)
+ Ship Date 

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[kyleb350]

Thank you! LOOKUPVALUE was the function I was looking for.

Shipping ETA = if(isblank(related(Ship Time.Warehouse)),LOOKUPVALUE ( Ship Time.[Shipping Days], Ship Time[Warehouse], "Default" ) + Ship Date,
 related(Shipping Days)
+ Ship Date