topic Re: Fill Blank Fields with another field - DAX in DAX Commands and Tips

Fill Blank Fields with another field - DAX

ajaydavidluke — Tue, 21 Sep 2021 12:49:23 GMT

Hi Community,

I am revisiting a problem DAX that is missing something slight!

I have applied the below formula but found it didnt like the Emb Estimated Delivery Date:

It doesnt give me the option to select from a different table?

So I have used SELECTEDVALUE to access the column from another table and have the below:

However the DAX does not return a value:

I'm pretty new to Power BI so my understanding is still developing 🙂

Re: Fill Blank Fields with another field - DAX

nandukrishnavs — Tue, 21 Sep 2021 13:09:12 GMT

ajaydavidluke

If there is a proper relationship then use RELATED()

RELATED(<column>)

Expected/Estimated Delivery Date = VAR prod_colour = [Prod/Colour] RETURN IF ( [Expected Delivery Date] - BLANK (), RELATED ( [Emb Estimated Delivery Date] ), [Expected Delivery Date] )

Re: Fill Blank Fields with another field - DAX

VahidDM — Tue, 21 Sep 2021 13:07:23 GMT

Hi ajaydavidluke

Try to use ISBLANK rather than [Expected Delivery Date] = BLANK(), and use Related if there is a relation between those tables or use Lookupvalue.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos✌️!!

Re: Fill Blank Fields with another field - DAX

ajaydavidluke — Wed, 22 Sep 2021 09:30:33 GMT

Thank you for your reply, there isnt a direct relationship between the 2 tables, relationships as below:

i) Orders > Prod/Colour = Next Supply Date > Prod/Colour

ii) Orders > Prod Base/Colour = Next Supply Base Product > Prod/Colour

I would like to create a new column within the 'Next Supply Date' named 'Expected/Estimated Delivery Date' I would like this newly created column to look at the 'Next Supply Date[Expected Delivery Date]' and if this date is blank/null/no value, to return back the date 'Next Supply Base Product'[Emb Estimated Delivery Date] else if 'Next Supply Date[Expected Delivery Date]' is not blank/null/no value to retain that value.

A Crystal report formula equivilant would be something like:

IF (ISNULL('Next Supply Date[Expected Delivery Date]')) THEN 'Next Supply Base Product'[Emb Estimated Delivery Date] ELSE 'Next Supply Date[Expected Delivery Date]'

Can anyone suggest the best course of action in this case?

Currently I'm using this

Which is returning:

What I should be seeing in 'Expected/Estimated Delivery Date' are dates pulled in from 'Emb Estimated Delivery Date' where 'Expected Delivery Date' has no value.

Many thanks in advance,
AJ

Re: Fill Blank Fields with another field - DAX

ajaydavidluke — Wed, 22 Sep 2021 09:32:08 GMT

Thank you for your reply, ISBLANK doesnt seem to work either, in order to give you more info I have included step by step my current chain of thought.........there isnt a direct relationship between the 2 tables, relationships as below:

i) Orders > Prod/Colour = Next Supply Date > Prod/Colour

ii) Orders > Prod Base/Colour = Next Supply Base Product > Prod/Colour

A Crystal report formula equivilant would be something like:

IF (ISNULL('Next Supply Date[Expected Delivery Date]')) THEN 'Next Supply Base Product'[Emb Estimated Delivery Date] ELSE 'Next Supply Date[Expected Delivery Date]'

Can anyone suggest the best course of action in this case?

Currently I'm using this

Which is returning:

What I should be seeing in 'Expected/Estimated Delivery Date' are dates pulled in from 'Emb Estimated Delivery Date' where 'Expected Delivery Date' has no value.

Many thanks in advance,
AJ

Re: Fill Blank Fields with another field - DAX

ajaydavidluke — Tue, 19 Oct 2021 09:11:16 GMT

Hi Andy,

Its me, you but in the future, I figured out the solution:

IF(

ISBLANK(RELATED('Next Supply Date'[Expected Delivery Date])),

RELATED('Emb Next Supply Date'[Base Product Supply Date]),

RELATED('Next Supply Date'[Expected Delivery Date])

)

Thank me with a kebab later 😄

Re: Fill Blank Fields with another field - DAX

Anonymous — Mon, 21 Feb 2022 02:56:55 GMT

Hahaha. Thank you for sharing future you.