Using FIRSTNONBLANK unsuccessfully.

[wooand]

I have data in three columns in table 'Results'.  The columns are:

A: Deal Reference 

B:  Deal Time

C: Deal Rate

All cells contain numbers, no text.

I need to identify the very first Deal Rate in the list, ordered by time.  I have had minor success using FIRSTNONBLANK, but it isn't returning the first rate when ordered by time.  It's returning something from the middle of the list with no obvious reason to me why that's the case!  

Can anyone help please?

[Greg_Deckler]

Can you post the DAX for your formula? And could you post some sample data? Are you looking for the firstnonblank for each Deal Reference?

[wooand]

Sure, here's a snip of the Excel data file as it stands:

It carries on in the same vein for several thousand lines.

By using = CALCULATE(FIRSTNONBLANK(Results[Dealt Rate], TRUE()) I get a value, but I want the first one in terms of the Trade Start Time.  I'm concerned that there may be a millisecond issue here to boot, but anyway, any help most welcome.

[Zubair_Muhammad]

HI @wooand

And that value returned would be the minimum of all the DEALT RATES??? Is it?

[wooand]

No, in this example I would want 1.1726 at the first Trade Time (it's ordered by Trade Time), but for some reason I'm getting some other rate from elsewhere in the data series.

[Zubair_Muhammad · Accepted solution]

HI @wooand

Try this formula

Formula =
VAR FirstNonBlankTradeStartTime =
    CALCULATE ( FIRSTNONBLANK ( ALL ( Results[Trade Start Time] ), TRUE () ) )
RETURN
    CALCULATE (
        FIRSTNONBLANK ( Results[Dealt Rate], 1 ),
        FILTER (
            ALL ( Results ),
            Results[Trade Start Time] = FirstNonBlankTradeStartTime
        )
    )

[wooand]

Nicely done Zubair.  That was way beyond me, so thanks.

[Zubair_Muhammad]

@wooand

I wrote this Article few days ago

http://www.excelnaccess.com/using-firstnonblank-lastnonblank-in-dax/

[wooand]

Clearly you are the expert!

[Zubair_Muhammad]

@wooand

Actually FirstNonBlank /LastNonBlank return the first/last nonblank value respectively in the column…..after sorting the column in its native Ascending Order….

[NN123]

Hi Zubair,

Is there a way to stop the native sorting of column in Ascending order? The dataset that I have already has it sorted in a particular order and I just want to pick the first record.  I am basically doing a lookup to get the first value in sample[ColC] for matching Col A in 'data' table.

mappedColC= CALCULATE (
FIRSTNONBLANK (sample[ColC],1 ),
FILTER ( ALL ( sample),sample[Col A]=data[ColA] )
)

The table sample below is already sorted in this order, sort by Col A Asc, then Col B desc. Then, pick the first value of ColC for each Col A. In the below sample the desired output would be for 'A'= XYZ, 'B'=KLM, 'C'=KLM, 'D'=ABC.   But as per your explanation, I now understand why I am getting 'A'=ABC. I tried to add the additional condition 

FILTER ( ALL ( sample),sample[Col A]=data[ColA] && sample[Col B]=TRUE )

 then it picks XYZ for A, but misses to pick KLM for 'C' and that retuns (blank). Please help.

Col A	Col B	ColC
A	TRUE	XYZ
A	TRUE	XYZ
A	FALSE	KLM
A	FALSE	ABC
B	TRUE	KLM
B	FALSE	XYZ
C	FALSE	KLM
D	TRUE	ABC