Forum Discussion

gdlrsilv3's avatar
gdlrsilv3
Regular Visitor
4 years ago
Solved

question about complicated data transformation

Hi,   I have a question on data transformation, I have the following scenario, in my imported invoices information I have part numbers that are multipack (a box with N single products) and also par...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    4 years ago

    Hi  gdlrsilv3 ,

     

    First create a column in Bom:

    Invoice number = LOOKUPVALUE('Sales'[InvoiceNum],'Sales'[item_code],'BOM'[ParentItem],blank())

    And create a table as below:

    Union = UNION('Sales',SELECTCOLUMNS('BOM',"InvoiceNum",'BOM'[Invoice number],"item_code",'BOM'[ChildItem],"qty",'BOM'[qty]))

    Then create a measure:

    Measure =
VAR _type =
    CALCULATETABLE (
        VALUES ( 'Item'[item_code] ),
        FILTER ( ALL ( 'Item' ), 'Item'[item_type] = "Single" )
    )
RETURN
    IF ( MAX ( 'Union'[item_code] ) IN _type, 1, BLANK () )

    Put measure in the filter pane and select measure is 1;

    And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my reply as a solution!

     

     

AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

It looks like you are ignoring the second ChildItem, 00010. Why do you pick 8 instead of 1 or 8+1?

  • gdlrsilv3's avatar
    gdlrsilv3
    Regular Visitor
    4 years ago

    I won't consider item 00010 because it is a carton, only items with item_type = 'Single' will be considered.

    • v-kelly-msft's avatar
      v-kelly-msft
      Community Support
      4 years ago

      Hi  gdlrsilv3 ,

       

      First create a column in Bom:

      Invoice number = LOOKUPVALUE('Sales'[InvoiceNum],'Sales'[item_code],'BOM'[ParentItem],blank())

      And create a table as below:

      Union = UNION('Sales',SELECTCOLUMNS('BOM',"InvoiceNum",'BOM'[Invoice number],"item_code",'BOM'[ChildItem],"qty",'BOM'[qty]))

      Then create a measure:

      Measure =
VAR _type =
    CALCULATETABLE (
        VALUES ( 'Item'[item_code] ),
        FILTER ( ALL ( 'Item' ), 'Item'[item_type] = "Single" )
    )
RETURN
    IF ( MAX ( 'Union'[item_code] ) IN _type, 1, BLANK () )

      Put measure in the filter pane and select measure is 1;

      And you will see:

      For the related .pbix file,pls see attached.

       

      Best Regards,
      Kelly

      Did I answer your question? Mark my reply as a solution!