Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago

DAX Formula - Mismatch

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.

General Comment
Need Help
Tutorial Requests

14 Replies

  • hnguy71's avatar
    hnguy71
    Super User
    1 year ago

    Hi Anonymous ,

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

    Table = CALCULATETABLE(VALUES(SaleseOrder[SaleseOrderNumber]), ISBLANK(Purshasing[OrderNumber]))
    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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's avatar
        hnguy71
        Super User
        1 year ago

        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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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 
    225976Direct to Customer25641
    265897Direct to Customer25643
    265389East25645
    265972General25649
    225978West 25647
    226594General

    25646

     

    Purchasing 

    Purchase order number WarehouseSales Order number 
    25641Direct to Customer225976
    25643General 265897
    25645General 265389
    25649East265972
    25647West225978
    25646Direct to Customer226594
    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      1 year ago

      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's avatar
        Anonymous
        Not applicable
        1 year ago

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

    • ryan_mayu's avatar
      ryan_mayu
      Super User
      1 year ago

      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's avatar
        Anonymous
        Not applicable
        1 year ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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!