DAX Formula - Mismatch

[Anonymous]

Hi, 

Could you please create a DAX formula that shows SalesOrders that have a warehouse that doesnt match Purchasing warehouse.

the tables i have are: 

SalesOrder :

- SalesOrderNumber

- Warehouse 

Purchasing :

- OrderNumber

-Warehouse

i basically want to create a table that shows me all sale order numbers that dont match their purchasing side.

[Anonymous]

Hi @Anonymous , hello Ashish_Mathur  , ryan_mayu  and hnguy71 , thank you for your prompt reply!

Is there any progress on this issue?

Could you please review the PBIX file created by Ryan Mayu?

Based on my testing, it works well, just as Ryan Mayu mentioned. If it doesn't work for you, could you please upload your sample file?

Thank you for your understanding!

[Anonymous]

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

[ryan_mayu]

@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 below

[Anonymous]

Thank 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"))).

[ryan_mayu]

could you pls provide the pbix file?

[Ashish_Mathur]

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.

[Anonymous]

Hi This is what i got : 

[Ashish_Mathur]

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]

thank you- how would i merge the two tables? and what is the expanded purchasing?

[Ashish_Mathur]

You are welcome.  I have already shared the M code with you.

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.

[hnguy71]

Hi @Anonymous ,

To create a table that shows all the orders that does not match:

Table = CALCULATETABLE(VALUES(SaleseOrder[SaleseOrderNumber]), ISBLANK(Purshasing[OrderNumber]))

[Anonymous]

Hi That doesnt show if the warehouses dont match. 

I want it to show Sale order Numbers where the purchase order number has a warehouse that doesnt match the sales order ; 

Sales Order 223454 - Warehouse ABC -> is matched to --> Purchase order 2345 - Warehouse xyz (so it would pick this up)

[hnguy71]

Hi @Anonymous ,

Then you would need to do the opposite. The DAX formula is essentially correct:

Table = CALCULATETABLE(VALUES(Purshasing[OrderNumber]), ISBLANK(SaleseOrder[SaleseOrderNumber]))

 
Without enough information or sample data, this is as much assistance as I can offer you.