Forum Discussion
help with DAX
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
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])
4 Replies
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])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
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,
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)"
