Forum Discussion

baBI123's avatar
baBI123
Helper II
8 years ago
Solved

Delete rows containing certain text

Hi all,  I have the above information already in POWER BI.  I need to know how I can eliminate the rows in the Part Number column that contain "shipping supplies", "shop supplies, ", "office ...
Need Help
  • parry2k's avatar
    parry2k
    8 years ago
    SUMMARIZE PO =
SUMMARIZE (
Filter('Purchase Order History', 
Not(
'Purchase Order History'[Part Number] 
in {
"Shipping Supplier", 
"Shop Supplies", 
"Office Supplies", 
"Handling"
}
)
)
,
'PURCHASE ORDER HISTORY'[Part Number],
"TOTAL COST", SUM ( 'PURCHASE ORDER HISTORY'[PN Total Cost] ),
"Quantity Ordered", SUM ( 'PURCHASE ORDER HISTORY'[Qty Ordered] ),
"PO list", CONCATENATEX ( 'PURCHASE ORDER HISTORY', 'PURCHASE ORDER HISTORY'[Purchase Order #], ", " )
)
parry2k's avatar
parry2k
Super User
8 years ago

change following in your formula after summarize:

 

Filter('Purchase Order History', 
Not(
'Purchase Order History'[Part Number] 
in {
"Shipping Supplier", 
"Shop Supplies", 
"Office Supplies", 
"Handling
}
)
)
baBI123's avatar
baBI123
Helper II
8 years ago

parry2k Where do I insert that into the DAX exactly?

  • parry2k's avatar
    parry2k
    Super User
    8 years ago

    just the bracket after summarize , replace 'Purchase Order History' with my formula then comma and continue on.

     

    If you past your formula as text i will update it, because you pasted image and i'm lazy to type all your formula in there :(

    • baBI123's avatar
      baBI123
      Helper II
      8 years ago

      parry2k done deal :) Thanks for helping me out!

      SUMMARIZE PO =
      SUMMARIZE (
      'PURCHASE ORDER HISTORY',
      'PURCHASE ORDER HISTORY'[Part Number],
      "TOTAL COST", SUM ( 'PURCHASE ORDER HISTORY'[PN Total Cost] ),
      "Quantity Ordered", SUM ( 'PURCHASE ORDER HISTORY'[Qty Ordered] ),
      "PO list", CONCATENATEX ( 'PURCHASE ORDER HISTORY', 'PURCHASE ORDER HISTORY'[Purchase Order #], ", " )
      )

      • parry2k's avatar
        parry2k
        Super User
        8 years ago
        SUMMARIZE PO =
SUMMARIZE (
Filter('Purchase Order History', 
Not(
'Purchase Order History'[Part Number] 
in {
"Shipping Supplier", 
"Shop Supplies", 
"Office Supplies", 
"Handling"
}
)
)
,
'PURCHASE ORDER HISTORY'[Part Number],
"TOTAL COST", SUM ( 'PURCHASE ORDER HISTORY'[PN Total Cost] ),
"Quantity Ordered", SUM ( 'PURCHASE ORDER HISTORY'[Qty Ordered] ),
"PO list", CONCATENATEX ( 'PURCHASE ORDER HISTORY', 'PURCHASE ORDER HISTORY'[Purchase Order #], ", " )
)