Forum Discussion

alfon650's avatar
alfon650
Frequent Visitor
7 years ago
Solved

FIRSTNONBLANK to generate a column

hello,

I must say that I am a beguinner to DAX, and so you might find this question simple . . .

I am quickly learning all the DAX functions, but I got stucked with Firstnonblank. I have written this formula (see printscreen), in order to go to the simplest behaviour of the formula, but I would have expected the first (smallest) value of the column repeated in all the rows  . . . instead this is what I get. Your help is welcome,

Alfonso (Barcelona)

 

 

 

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    Hi Alfonso,

     

    That's a trick done by the context. Please refer to dax/firstnonblank-function-dax. The column will be filtered by the context. If it's like this, the column is filtered by row context.

    Column = FIRSTNONBLANK(Table1[Column2], 1)

    If it's like this, the column is filtered by filter context. 

    Column = calculate(FIRSTNONBLANK(Table1[Column2], 1))

    So we need to remove the context like below. 

    First NON blank = calculate(firstnonblank(append1[ingresos.2];1), all('table'))

    Or,

    Column = FIRSTNONBLANK(all(Table1[Column2]), 1)

    FIRSTNONBLANK-to-generate-a-column2

     

    Best Regards,
    Dale

5 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi alfon650,

     

    Please refer to the snapshot below. Yours could be this one.

    First NON blank = calculate(firstnonblank(append1[ingresos.2];1), all('table'))

     

    FIRSTNONBLANK-to-generate-a-column

     

     

    Best Regards,
    Dale

    • alfon650's avatar
      alfon650
      Frequent Visitor
      7 years ago

      Hi Dale,

      thanks for your kind help, but my question is more to do with why my DAX formula doesn't provide what is expected.

      I have tried your suggestion and it works, in fact I had previously nested a Firstnonblank inside a Calculate, and it works well. I have googled about it, and the authors never use Firstnonblank alone. 

      So the question is why this performance? could it be used on its own? this is what I need to understand its behaviour.

      thanks for your support,

      Alfonso (Barcelona)

       

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        7 years ago

        Hi Alfonso,

         

        That's a trick done by the context. Please refer to dax/firstnonblank-function-dax. The column will be filtered by the context. If it's like this, the column is filtered by row context.

        Column = FIRSTNONBLANK(Table1[Column2], 1)

        If it's like this, the column is filtered by filter context. 

        Column = calculate(FIRSTNONBLANK(Table1[Column2], 1))

        So we need to remove the context like below. 

        First NON blank = calculate(firstnonblank(append1[ingresos.2];1), all('table'))

        Or,

        Column = FIRSTNONBLANK(all(Table1[Column2]), 1)

        FIRSTNONBLANK-to-generate-a-column2

         

        Best Regards,
        Dale