Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Solution Sage
Solution Sage

@userdata 

 

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

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.