Forum Discussion

Shineccx007's avatar
Shineccx007
Icon for Helper II rankHelper II
2 years ago

DAX Calculate Sum under multiple columns in a table

Hello!

 

I created a measure on calculating Sum quantity under several column filters under the same table. 

 

Inventory On Hand =
CALCULATE(
    SUM(WarehouseEntries[Quantity]),
    FILTER(WarehouseEntries, WarehouseEntries[Location_Code] = "PLANT" || WarehouseEntries[Location_Code] = "CF TORBRAM" || WarehouseEntries[Location_Code] = "PANEX" ||WarehouseEntries[Location_Code] = "WAREHOUSE" || WarehouseEntries[Bin_Code] <> "CAKE C" || WarehouseEntries[Bin_Code] <>"CD C" || WarehouseEntries[Bin_Code]<> "COSTCO C" || WarehouseEntries[Bin_Code]<> "DISPOSE" || WarehouseEntries[Bin_Code]<>"FMIX C" || WarehouseEntries[Bin_Code]<> "GB C" || WarehouseEntries[Bin_Code]<>"GB MIX"|| WarehouseEntries[Bin_Code]<>"JELLY C" || WarehouseEntries[Bin_Code]<>"OUTPUT" || WarehouseEntries[Bin_Code]<>"OV1 C" ||WarehouseEntries[Bin_Code]<>"OV2 C" ||WarehouseEntries[Bin_Code]<>"PD01-1" || WarehouseEntries[Bin_Code]<>"PDWH"|| WarehouseEntries[Bin_Code]<>"PENDING" || WarehouseEntries[Bin_Code]<>"PENDING2"|| WarehouseEntries[Bin_Code]<> "PREMIX"|| WarehouseEntries[Bin_Code]<>"PREMIX C" || WarehouseEntries[Bin_Code]<>"RD C"||WarehouseEntries[Bin_Code]<>"SAMPLES"|| WarehouseEntries[Bin_Code]<>"SP C" || WarehouseEntries[Bin_Code]<>"STAGE"||WarehouseEntries[Bin_Code]<>"STORAGE1"|| WarehouseEntries[Bin_Code]<>"SUPPORT C"|| WarehouseEntries[Bin_Code]<>"SYRUP C" || WarehouseEntries[Bin_Code]<>"DOUGH C" || WarehouseEntries[Bin_Code]<>"TART C" || WarehouseEntries[Bin_Code]<>"XBMIX C"|| WarehouseEntries[Bin_Code]<>"XCONSIGN" || WarehouseEntries[Bin_Code]<>"XFC C"|| WarehouseEntries[Bin_Code]<>"XSYRUP" || WarehouseEntries[Bin_Code]<>"SHIP"|| WarehouseEntries[Bin_Code]<>"FMIX C")
)
 

 

But when I drag the item code, location code, bin code and quantity to the visual table under Power BI, the bin code is still showing the data I don't need. And the quantity didn't seem any different to what I have from the original quantity Sum. Anyone can have a look at what's going on?

 

Replicating Tableau Parameter/Filter in Power BI 

 

Thank you!

 

Shine

data protection
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    || is the OR operator, maybe try the AND operator - &&

    • Shineccx007's avatar
      Shineccx007
      Icon for Helper II rankHelper II
      2 years ago

      Hi Thomas,

       

      After I changed all the || to &&, I cannot see any valid data in the matrix table.

       

      Did I make anything wrong?

       

      Shine

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    My misitake, it does look like you need the OR (||) operator.
    Maybe the issue is too many comparisons in one Measure.
    Try to break it up into multiple measures and test one condition at a time until you see the issue come up