Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Help - Show when data doesnt match

Hi, 

 

Could you please create me a powerbi measure to filtre data to only show sales order numbers where the warehouse doesnt match the po numbers warehouse putting a Y for Mismatched and N for matched- the data : Purchasing ;Warehouse . SalesOrderItems; Warehouse, Order Number, PO Number. there is a relationship between SaleOrderItems; PO Number and Purchasing: Order Number.

 

sample Data: 

SalesOrdersItems 

Order NumberWarehouse PO 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

 

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    1 year ago

    you can try this

     

    Column =
    var _w=maxx(FILTER(Purchasing,Purchasing[Purchase order number ]=SalesOrdersItems[PO Number ]&&SalesOrdersItems[Order Number]=Purchasing[Sales Order number ]),Purchasing[Warehouse])
    return if( _w=SalesOrdersItems[Warehouse ],"Y","N")
     
    pls see the attachment below
     

6 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    1 year ago

    you can try this

     

    Column =
    var _w=maxx(FILTER(Purchasing,Purchasing[Purchase order number ]=SalesOrdersItems[PO Number ]&&SalesOrdersItems[Order Number]=Purchasing[Sales Order number ]),Purchasing[Warehouse])
    return if( _w=SalesOrdersItems[Warehouse ],"Y","N")
     
    pls see the attachment below
     
    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi, I got an error: The syntax for 'return' is incorrect. (DAX(maxx(FILTER(Purchasing,Purchasing[Order Number]=SalesOrdersItems[PO Number]&&SalesOrdersItems[Order Number]=Purchasing[Sales Order No]),Purchasing[Warehouse])return if( _w=SalesOrdersItems[Warehouse],"Y","N"))).

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi Anonymous ,

         

        I reproduced your error below.

        Your problem is that the var definition is missing. After adding "var _w=" and it worked.

        Hope it helps.

         

        Best Regards,

        Stephen Tao

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

         

  • FreemanZ's avatar
    FreemanZ
    Super User
    1 year ago

    hi Anonymous ,

     

    for "relationship between SaleOrderItems; PO Number and Purchasing: Order Number.", which is one side?

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago