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 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
        )
    )
wooand's avatar
wooand
Helper 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.

Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
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
        )
    )
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    wooand

     

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

     

     

     

    • NN123's avatar
      NN123
      Regular Visitor
      8 years ago

      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 ACol BColC
      ATRUEXYZ
      ATRUEXYZ
      AFALSEKLM
      AFALSEABC
      BTRUEKLM
      BFALSEXYZ
      CFALSEKLM
      DTRUEABC
  • wooand's avatar
    wooand
    Helper II
    8 years ago

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