Multiple filters, trying to include not equal to

[noannish]

The DAX I list below is working fine, but I'm off my DAX game today and when I try to also exlude some values from a different column in the same table I end up just summing everything that doesn't have them which gives every item the same sum.

I want to exclude any instances where udv_SalesItems[Item Produc ID ud5] is equal to "1307", "1320", "1321", or "1322"

Current code without exclusion:

2018 Item Count = CALCULATE(

                                  SUM(

                                     udv_SalesItems[Value Item Qty]),

                                     udv_SalesItems[Item_Class] IN {"FG1001", "FG1002", "FG1003", "FG1005"},

                                     udv_SalesItems[Date_Year] = 2018

                                  )

[az38]

Hi @noannish 

try

2018 Item Count = CALCULATE(
      SUM(
                                     udv_SalesItems[Value Item Qty]),
                                     udv_SalesItems[Item_Class] IN {"FG1001", "FG1002", "FG1003", "FG1005"},
                                     udv_SalesItems[Date_Year] = 2018,
NOT (udv_SalesItems[Item Produc ID ud5] IN {"1307", "1320", "1321",  "1322"})
                                  )

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn

[noannish]

Thank you for trying, unfortunately it still sums everything where those ud5s do not exist, giving every item the same amount. Not the end of the world that I can't figure out how to do it within the measure, just would have been nice.

[az38]

@noannish 

maybe

2018 Item Count = CALCULATE(
      SUM(udv_SalesItems[Value Item Qty]),
      FILTER(ALL(),
     AND(AND(udv_SalesItems[Item_Class] IN {"FG1001", "FG1002", "FG1003", "FG1005"},
                                     udv_SalesItems[Date_Year] = 2018),
NOT (udv_SalesItems[Item Produc ID ud5] IN {"1307", "1320", "1321",  "1322"}))
      )
)

do not hesitate to give a kudo to useful posts and mark solutions as solution