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!

     

     

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!