Forum Discussion

wooand's avatar
wooand
Helper II
8 years ago
Solved

Using FIRSTNONBLANK with a slicer.

I'm using this to identify the first value in a selection filtered by a slicer.   Arrival Price = IF ( ISFILTERED ( Sheet1[Deal Reference Id] ) && HASONEVALUE ( Sheet1[Deal Reference Id] ), FIRS...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    Hi wooand

     

    FirstNonBlank is like using a MIN function and will return the lowest value rather than the first item in a sequence.

  • wooand's avatar
    wooand
    8 years ago

    In fact this is possible.  The excellent Zubair Mohammed provided the following:

     

    Arrival Price =
    VAR FirstNonBlankTradeStartTime =
    CALCULATE ( FIRSTNONBLANK ( VALUES ( Algo[Trade Start Time] ), TRUE () ) )
    RETURN
    CALCULATE (
    FIRSTNONBLANK ( Algo[Dealt Rate], 1 ),
    FILTER ( Algo, Algo[Trade Start Time] = FirstNonBlankTradeStartTime )
    )

     

    Which works well.

     

     

wooand's avatar
wooand
Helper II
8 years ago

In fact this is possible.  The excellent Zubair Mohammed provided the following:

 

Arrival Price =
VAR FirstNonBlankTradeStartTime =
CALCULATE ( FIRSTNONBLANK ( VALUES ( Algo[Trade Start Time] ), TRUE () ) )
RETURN
CALCULATE (
FIRSTNONBLANK ( Algo[Dealt Rate], 1 ),
FILTER ( Algo, Algo[Trade Start Time] = FirstNonBlankTradeStartTime )
)

 

Which works well.