Forum Discussion
Setting Default DAX Value If Lookup Is Blank
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)
Anonymous 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.
2 Replies
- parry2k
Super User
Anonymous 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.