Forum Discussion

wooand's avatar
wooand
Helper II
8 years ago
Solved

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!  

 

Can anyone help please?

 

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

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

10 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    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's avatar
      wooand
      Helper II
      8 years ago

      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.