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 ) )
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
