New Column With LOOKUPVALUE DAX

[fmarthidalgo]

Good morning all! 

I'm having trouble trying to create a new column based on the criteria of the two prior columns. 

I have column A and Column B. I need to find a way to create column C (Type2) that finds the non-blank field of culumn B and assigns that same value to all cells with the same invoice number in coulmn A, as shown in column C (filled this in manually for the example). Tried a LOOKUPVALUE with FILTERS and I'm still not able to create column C. 

If someone can shed some light into this, it would be very much appreciated! 

[v-yangliu-msft · Accepted solution]

Hi  @fmarthidalgo ,

Whether the connection mode of your data source is Direct Query connection mode, the Earliest() function is not supported on Direct Query connection mode

You might consider using the following DAX:

VAR keyword (DAX) - DAX | Microsoft Learn

Column =
var _number=[INVNUMB]
return
MAXX(
    FILTER(ALL('Table'),
    'Table'[INVNUMB]=_number),[Type])

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak]

@fmarthidalgo , A new column in DAX

Type2 = maxx(filter(Table, [INVNUMB] = earlier([INVNUMB]) && not(isblank([Type1])) ), [Type1])

[fmarthidalgo]

Hi @amitchandak,

Thank you for your response, just what I'm looking for in DAX. As suggested above is not working. EARLIER does not let me pull in the [INVNUMB] column and NOT(ISBLANK([Type1])) is asking for a SUM, COUNT, MAX, etc. expression. 

Thanks again!