help with DAX

[Gareth_Hunt]

Hi Guys,

this may be on here twice thought I was signed in before posting but now cannot seethe original post so here goes

I would like to  do something that feels very simple but Im new to DAX 

I have 3 columns of imported data

TYPE	Invoice	Order	Salesman
IN	55567	12345	JIM
IN	55567	12345	JIM
CR	99998	55567
CR	99999	55568

what I would like to do is create a 4th column in the query editor that 

TYPE	Invoice	Order	Salesman	Salesman complete
IN	55567	12345	JIM	JIM
IN	55567	12345	JIM	JIM
CR	99998	55567		JIM
CR	99999	55568

 I would like to create a DAX equation that returns the following 

if TYPE = "IN" then return Salesman value

if TYPE = "CR" then lookup ORDER against INVOICE and return Salesman

IF TYPE = "CR" lookup returns no value then return (blank)

I would use vlookup in excell but whath is the DAX equvalient?

Many thanks

Gareth

[amitchandak · Accepted solution]

@Gareth_Hunt , Create a new column like

new column =
var _salesm = maxx(filter(Table,table[Order] =earlier([Invoice])),[Salesman])
return
if([TYPE] ="IN",[Salesman],[_salesm])

[Gareth_Hunt]

Hi Amitchandak,

Ok so I have created a new column as prescribed and im looking to apply your example and its works well thank you guys 

NB removed the [] from around _salesm as it was erroring out in case anyone else looks at this in the future 

"new column =
var _salesm = maxx(filter(Table,table[Order] =earlier([Invoice])),[Salesman])
return
if([TYPE] ="IN",[Salesman],_salesm)"

[Gareth_Hunt]

Hi Sorry  -I dont understand your expression, this is as far as I have got - I have no table reference??

thanks for your help though

[ibarrau]

Hi, that is the "transform data" view. That query editor is for power query language transformations. Amit has sent you a DAX formula as you have asked. Check this doc that will help you build Amit's solution to validate it.

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-tutorial-create-calculated-columns

Regards,