topic Re: find latest nonblankvalue and override with text in DAX Commands and Tips

find latest nonblankvalue and override with text

Anonymous — Mon, 01 Aug 2022 20:08:30 GMT

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?

The file can be found here:

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

Re: find latest nonblankvalue and override with text

daXtreme — Tue, 02 Aug 2022 08:46:22 GMT

Anonymous

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

Re: find latest nonblankvalue and override with text

Anonymous — Tue, 02 Aug 2022 08:53:15 GMT

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.

Re: find latest nonblankvalue and override with text

daXtreme — Tue, 02 Aug 2022 13:35:53 GMT

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

Re: find latest nonblankvalue and override with text

Anonymous — Tue, 02 Aug 2022 18:30:13 GMT

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

Re: find latest nonblankvalue and override with text

Anonymous — Tue, 02 Aug 2022 18:32:30 GMT

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.

Re: find latest nonblankvalue and override with text

Anonymous — Tue, 02 Aug 2022 21:32:52 GMT

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

Re: find latest nonblankvalue and override with text

v-xiaotang — Tue, 23 Aug 2022 10:00:09 GMT

Hi Anonymous

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]))

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.