Forum Discussion
DAX Formula - Mismatch
Please find some sample data, basically i want it to pick up when the warehouse doesnt match ie. it should pick up - Sales order number 265897 , 265389 , 265972 , 226594 from the data set.
SalesOrder
| Sales Order number | Warehouse | Purchase Order Number |
| 225976 | Direct to Customer | 25641 |
| 265897 | Direct to Customer | 25643 |
| 265389 | East | 25645 |
| 265972 | General | 25649 |
| 225978 | West | 25647 |
| 226594 | General | 25646
|
Purchasing
| Purchase order number | Warehouse | Sales Order number |
| 25641 | Direct to Customer | 225976 |
| 25643 | General | 265897 |
| 25645 | General | 265389 |
| 25649 | East | 265972 |
| 25647 | West | 225978 |
| 25646 | Direct to Customer | 226594 |
Hi,
This M code works
let Source = Table.NestedJoin(Salesorder, {"Sales Order number "}, purchasing, {"Sales Order number "}, "purchasing", JoinKind.LeftOuter), #"Expanded purchasing" = Table.ExpandTableColumn(Source, "purchasing", {"Warehouse"}, {"Warehouse1"}), #"Filtered Rows" = Table.SelectRows(#"Expanded purchasing", each ([#"Warehouse "] <> [Warehouse1])) in #"Filtered Rows"Hope this helps.
thank you- how would i merge the two tables? and what is the expanded purchasing?
You are welcome. I have already shared the M code with you.
Hi This is what i got :
My code has to be pasted in the Advanced Editor window which is available under View. Ensure that the table and column names in your actual file are the same as those which are in the M code which i shared.
Anonymous
you can try to create a column
Column =VAR _w=maxx(FILTER(Purchasing,Purchasing[Purchase order number ]=SalesOrder[Purchase Order Number ]),Purchasing[Warehouse])return if(_w<>SalesOrder[Warehouse ],"y")pls see the attachment belowThank you, however it says : The syntax for 'return' is incorrect. (DAX(maxx(FILTER(Purchasing,Purchasing[Purchase order number ]=SalesOrder[Purchase Order Number ]),Purchasing[Warehouse])return if(_w<>SalesOrder[Warehouse ],"y"))).
could you pls provide the pbix file?
