cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper II

Using FIRSTNONBLANK unsuccessfully.

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!

1 ACCEPTED SOLUTION
Community Champion

HI @wooand

Try this formula

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

Regards
Zubair

10 REPLIES 10
Super User

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?

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper II

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.

Community Champion

HI @wooand

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

Regards
Zubair

Helper II

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.

Community Champion

HI @wooand

Try this formula

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

Regards
Zubair

Helper II

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

Community Champion

@wooand

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

Regards
Zubair

Helper II

Clearly you are the expert!

Community Champion

@wooand

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

Regards
Zubair

Regular Visitor

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

Announcements

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

Power BI Monthly Update - April 2024

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

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors