How to find first non blank value in a calculated column

[nicole_91]

I have tried everything and cannot figure out how to calculate firstnonblank value of a calculated column. I have read elsewhere that the FIRSTNONBLANK function only works on source columns. If this is true, then is there a workaround?

I thought that I could use the FIRSTNONBLANK function on the Time of Transit column (even though there are no blank values) but filter to get the first value of the Transit Day>=0.95 column based on the FILTER function. Sample data set is below:

Columns not bolded are source columns. Bolded columns are measure columns. 

Time of Transit on Truck (in Days)	Quantity of Fruit Boxes	Running Total	%Total of Boxes	Transit Days >=0.95
3	1	1	0.04
4	8	9	0.35
7	9	18	0.69
10	2	20	0.77
12	2	22	0.85
14	1	23	0.88
16	2	25	0.96	16
17	1	26	1.00	17

[Anonymous]

@nicole_91 

Would you please be more clear on what it is you really want to calculcate? Do you want a measure? A column in a base table? And what's the precise definition of the quantity you want? Currently, I've only got a vague idea about your issue. For instance, if you look for the first Time Of Transit on Truck where Running Total % is >= 95%, then it's easy to find this value and make it into a measure or in the calculated column. But you've got 16 and 17 in the last column. Why?

[nicole_91]

Hi @Anonymous 

Yes so basically what I want to do is, based on the transit time (4 days, 5 days, 8 days, etc.), I want to see how many days it would take to reach 95% of fruit boxes delivered. So I have the running total, and the last column populates the days that have reached that 95% delivery percentage (thus why there are only two values). However, I want to just retrieve the first value once the %total reaches 95% or above (in this instance, 16 days). In 16 days, the delivery percentage reached 96%, thus meeting the criteria. This is a continuation of an earlier question that I posted on here, but never got an answer. Does this make sense?

[dedelman_clng]

Hi @nicole_91 -

First off there's really no such thing as a "measure column" - there are source columns and calculated columns, but those are only populated/updated when the data is refreshed.  A measure can be *used* as a column in table or matrix visual, but it acts very different than a calculated column, in that it recalculates any time the context changes.  Sounds like you're wanting to use a measure here.

Now, LASTNONBLANK and FIRSTNONBLANK bring back a one row/one column table based on the source table/column you are using. These can't generally be used as a result or output (unless you're creating a calculated table) - they're generally used as modifiers inside code.

However, they have related functions LASTNONBLANKVALUE and FIRSTNONBLANKVALUE, which is what you want to get the actual value returned, not which date/order/etc has the last/first blank value.

All that being said, can you share the code for the measures you currently have, and if possible what your data model looks like (if it's not a single table model like you indicate above)? Any information you can provide (a sample report file with sensitive data removed is always best, but any of the other things I mentioned would be a good start) would help point toward the solution you seek.

Hope this helps

David

[nicole_91]

The Quantity of Boxes is a calculated column (again, done in PBI Desktop when transforming data), and it calculates the quantity by the , delimiter.

Oddly, I had to end up summing the Quantity of Boxes calculated column when brought into the visual rather than count because then it would still only count one value in the instances where there were more than one. 

[nicole_91]

Hello @dedelman_clng!

Yes, I should have just said measure. That's what it is. Thank you for clarifying! Okay so I should rephrase. The first two columns are calculated columns that I created when I transformed the data in PBI Desktop. The rest of the columns are measures.

[Table]: FTBxs_Cumulative

Running Total =

VAR RunningTotal= CALCULATE([Quantity of Fruit Boxes], FILTER(ALL(FTBxs_Cumulative([Transit Time on Truck]), FTBxs_Cumulative[Transit Time on Truck]<=MAX(FTBxs_Cumulative[Transit Time on Truck])))

RETURN IF(COUNTROWS(FTBxs_Cumulative), RunningTotal)

%Total of Boxes = DIVIDE([Running Total],[SumValuebyday])

Transit Days >=0.95 = IF([Designation Measure]=1, SELECTEDVALUE(FTBxs_Cumulative([Transit Time in Truck]), BLANK())

I did not include these measures in the table, but it's used to calculate %Total of Boxes and Transit Days>=0.95:

SumValuebyday = CALCULATE(SUM([Quantity of Fruit Boxes]),ALLSELECTED(FTBxs_Cumulative[Transit TIme on Truck]))

Designation Measure = IF([%Total of Boxes]>=0.95, 1, 0)

[dedelman_clng · Accepted solution]

Hi @nicole_91 - 

Try a couple of things:

1) Change [Designation Measure] to return BLANK instead of 0 as the "else"

2) See if this measure works in your complete data model (in this case we are using FIRSTNONBLANK not FNBVALUE because you want the value from the key column)

FNB =
MAXX (
    FIRSTNONBLANK ( FTBxs_Cumulative[Transit Time on Truck], [Designation Measure] ),
    FTBxs_Cumulative[Transit Time on Truck]
)

 

Your full model may be more complex and need some additional coding, but hopefully this sends you in the right direction.

Hope this helps,

David

[nicole_91]

OH MY GOODNESS!!! THANK YOU!!!! IT WORKED!!!! I am SO EXCITED!!!! Thank you, @dedelman_clng!!!!!