Forum Discussion

wooand's avatar
wooand
Icon for Helper II rankHelper 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 De...
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
        )
    )
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity 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
    Icon for Helper II rankHelper 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.

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Icon for Community Champion rankCommunity Champion
      8 years ago

      HI wooand

       

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

      • wooand's avatar
        wooand
        Icon for Helper II rankHelper II
        8 years ago

        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.