topic Re: Fill in blanks from other rows in DAX Commands and Tips

Fill in blanks from other rows

Anonymous — Fri, 02 Sep 2022 10:51:53 GMT

hello community

i have two columns the first column have the same value several times , the next column can have two values one of them is a blank i want to fill this blank with other values from other rows if exist a non blank value if no to leave it a blank as it is ,
anyway to do that ?
thanks

Re: Fill in blanks from other rows

tamerj1 — Fri, 02 Sep 2022 11:13:34 GMT

Hi Anonymous

you can use

Drop Measure =
CALCULATE ( MAX ( TableName[drop] ), ALLEXCEPT ( TableName, TableName[phone] ) )

Re: Fill in blanks from other rows

Anonymous — Fri, 02 Sep 2022 11:30:53 GMT

thanks tamer it worked but i still see some annomalies for example after i ordered by phone number,
the phone number that ends with 3392 is filled even thought there is no other match for it so it should remain blank

Re: Fill in blanks from other rows

tamerj1 — Fri, 02 Sep 2022 11:48:55 GMT

Anonymous
That is strange. It should not happen.
Both columns are from the same table? Seems to a filter context issue. Also you may check if same number exist but preceded by a space.

Re: Fill in blanks from other rows

Anonymous — Fri, 02 Sep 2022 12:17:33 GMT

well actually i put some filters on the previous Vizz one of them was the date for example i took the period of August and there was only one input for that phone number , but in june there was other enteries , so can i modify the formula for example to take these filters into considerations ?

Re: Fill in blanks from other rows

tamerj1 — Fri, 02 Sep 2022 12:30:00 GMT

Anonymous
In this case we call the Date column as a filter safe column. Filter safe columns shall be added to the ALLEXCEPT argument inside CALCULATE:

Drop Measure = CALCULATE ( MAX ( TableName[drop] ), ALLEXCEPT ( TableName, TableName[phone], TableName[Creation Date] ) )

Re: Fill in blanks from other rows

Anonymous — Fri, 02 Sep 2022 12:43:31 GMT

well i added two columns as filter the results now are much better , but the date filter is working by day , instead i need the filter to be by the specific month and the other campaign filter to be to specific campaigns can i modify these filters , thank you sooooo much for your patients we are almost there

Re: Fill in blanks from other rows

tamerj1 — Fri, 02 Sep 2022 12:57:03 GMT

Anonymous
Usually these columns ad there in the Date table. Since you don't have a date table you can add a new column to your fact table.
Year Month = YEAR ( [Creation Date] ) * 100 + MONTH ( [Creation Date] )

Then you can add this column to ALLEXCEPT instead of the date.

Re: Fill in blanks from other rows

Anonymous — Fri, 02 Sep 2022 13:07:49 GMT

well that worked , thaaanks Alooooot tamerj1