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
        )
    )
Zubair_Muhammad's avatar
Zubair_Muhammad
Community 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
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.