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! 

[fmarthidalgo]

Hi @BiNavPete,

I appreciate the help a lot! 

I cannot upload an excel file so I'll paste the data below. Is there a way of doing this with a DAX fuction instead of a power query refference?

INVNUMB	TYPE	TYPE2
INV0010523		APT
INV0010523		APT
INV0010523		APT
INV0010523		APT
INV0010523		APT
INV0010523	APT	APT
INV0010523		APT
INV0010523		APT
INV0010523		APT
INV0040652		LDS
INV0040652		LDS
INV0040652		LDS
INV0040652		LDS
INV0040652		LDS
INV0040652		LDS
INV0040652		LDS
INV0040652	LDS	LDS
INV0040652		LDS
INV0040652		LDS
INV0040652		LDS

Thank you! 

[BiNavPete]

Hi  @fmarthidalgo

See attached PBIX with a Power query and a DAX solution.

The PQ solution is different than my earlier post.
Sort the data by InvNumb Ascending and Type Descending then fill down.
I've tidied up the data from your table initially by replacing blank with null.
I would have expected null where no Type exists in row and this is probably due to pasting the HTML table you provided.

The DAX solution applies the following calculated column:

Type_DAX =

    CALCULATE(Max(Invoice[TYPE]),

            FILTER(Invoice,Invoice[INVNUMB]<=EARLIER(Invoice[INVNUMB])))

This uses the EARLIER function to identify previous rows in the table.

PBIX Here

Personally I would use the PQ method as this is likely to be faster on a large dataset and it pushes the data transformation upstream which is always good practice.

Hope that solves your issue.

Pete
 

[BiNavPete]

Hi @fmarthidalgo

In Power Query reference the table and filter non blanks on Type Column.

(Table A)

Then reference the table again and merge on Invoicenumber int Table A, looking up type.

If you can share the data in Excel format, I'll write the script

Pete

[fmarthidalgo]

Thank you @BiNavPete,

This is great! The PQ way does work better on my full dataset! 

Thank you for the help, much appreciated!