Forum Discussion
Using FIRSTNONBLANK unsuccessfully.
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 ) )
Actually FirstNonBlank /LastNonBlank return the first/last nonblank value respectively in the column…..after sorting the column in its native Ascending Order….
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 A | Col B | ColC |
| A | TRUE | XYZ |
| A | TRUE | XYZ |
| A | FALSE | KLM |
| A | FALSE | ABC |
| B | TRUE | KLM |
| B | FALSE | XYZ |
| C | FALSE | KLM |
| D | TRUE | ABC |