cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
userdata
Helper IV
Helper IV

find latest nonblankvalue and override with text

Hi,

 

I have been struggling for a while with this one.

 

So I have a table with mean, std and date. The issue is that I need to lookup where there are values for mean the date and see if for date or the earlier date which is not blank and override the null in std with the new text value.

So for example for the first one on 10 may 17 there is a mean and i need to override the null in std, when i check in the table i see on 10 may 17 there is a null for std and the first nonblankvalue that is before 10 may is 9 may 17 with y. The null in the table for std has to be overwritten with y. How can I do that?

 

 

 

userdata_0-1659384272702.png

The file can be found here:

file/Override latest nonblankvalues with text values.pbix at master · userdata21/file (github.com)

1 ACCEPTED SOLUTION
v-xiaotang
Community Support
Community Support

Hi @userdata 

Thanks for reaching out to us.

create the measures below,

mindiff = 
var _v1=CALCULATE(MAX(Table2[date]),FILTER(ALL(Table2),Table2[date]<MIN(Table1[date])))
var _v2=CALCULATE(MIN(Table2[date]),FILTER(ALL(Table2),Table2[date]>MIN(Table1[date])))
return SWITCH(TRUE(),
ISBLANK(_v2)&&_v1<>BLANK(),_v1,
ISBLANK(_v1)&&_v2<>BLANK(),_v2,
datediff(_v1,MIN(Table1[date]),DAY) <datediff(MIN(Table1[date]),_v2,DAY),_v1,
_v2) 
Measure = 
CALCULATE(MAX(Table2[std]),FILTER(ALL(Table2),Table2[date]=[mindiff]))

vxiaotang_0-1661248801781.png

 

Best Regards,

Community Support Team _Tang

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

View solution in original post

7 REPLIES 7
v-xiaotang
Community Support
Community Support

Hi @userdata 

Thanks for reaching out to us.

create the measures below,

mindiff = 
var _v1=CALCULATE(MAX(Table2[date]),FILTER(ALL(Table2),Table2[date]<MIN(Table1[date])))
var _v2=CALCULATE(MIN(Table2[date]),FILTER(ALL(Table2),Table2[date]>MIN(Table1[date])))
return SWITCH(TRUE(),
ISBLANK(_v2)&&_v1<>BLANK(),_v1,
ISBLANK(_v1)&&_v2<>BLANK(),_v2,
datediff(_v1,MIN(Table1[date]),DAY) <datediff(MIN(Table1[date]),_v2,DAY),_v1,
_v2) 
Measure = 
CALCULATE(MAX(Table2[std]),FILTER(ALL(Table2),Table2[date]=[mindiff]))

vxiaotang_0-1661248801781.png

 

Best Regards,

Community Support Team _Tang

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

userdata
Helper IV
Helper IV

it is not about finding the first value or latest value and filling it with that value, but i need to dynmically check when date and mean has a value and lookthat value up ifor std where there was a value on that day or ealier and take the first with each row.

 

date |  mean                                                                  date | std

27.4     34                                                                        24.4    c

12.3     12                                                                        12.3   blank

                                                                                         10.3 x

                                                                                           9.3  b

 

 

 

output i want to see:

 

date |  mean  | std                                                                

27.4     34        c                                                               

12.3     12        x

 

there are two different table 1 and 2 and what i need to do is lookup if date in first table is 12.3 then i need to check in table 2 if there is a std for 12.3 if not i have to take the latest value that is not blank, in this case it would be x.

How would I do that in DAX

userdata
Helper IV
Helper IV

@daXtreme I do not fill in the blanks with backward values. I think maybe I have not explained it very well. But what i have is i have 3 columns, date, mean and everytime there is a mean that is blank is where we have values for std. What I need to do is check the date for when there is a mean lets say 7 august there is a value for mean and then in the same date column i have to check where std is not blank and then find the first instance on that date or before the value for std and put that value in there. So in std column the earlier date there is a value might be 5 august which is c for example. Then in the table it has to say c next to mean and date

userdata
Helper IV
Helper IV

Hi @daXtreme  i do not need to fill in the blanks. I have to lookup the dates and check the latest value before the date where mean has a value and look up with std for that date or the latest date before ther is a value.

As I said, Power Query has a special method for this: filling blanks with forward/backward values.

daXtreme
Super User
Super User

@userdata 

 

OK, why can't you use Power Query to fill in the blanks? It's a one-click operation.

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors