Forum Discussion
Anonymous
6 years agoNot applicable
Filter Formula Help: And, with OR and Not Equal To
Hi everyone,
I need help creating a Calculate measure that contains AND, OR, and NOT EQAL TO.
I've got a bunch of products that I am trying to group into 3 categories: high quaility, b, and other. And then find the volumes for each of these categories.
I was able to create measures to get the first 2 categories (high quality & b). I need help with the other category.
Here is my attempt.
// Gets a specefic a product qty
High Quality A Product Qty = CALCULATE(
SUM(FactCPRGradesheetDetail[InvoiceQuantity]),
FILTER(DimProduct,
DimProduct[MainProductName] = "A1"
)
)
// gets all of the b product qty
B Product Qty = CALCULATE(
SUM(FactCPRGradesheetDetail[InvoiceQuantity]),
FILTER(DimProduct,
DimProduct[NationalProductRollup] = "B"
)
)
// NEW MEASURE ATTEMPT:
// Want it to grab the rest of the products not mentioned above
Other Products Qty = CALCULATE(
SUM(FactCPRGradesheetDetail[InvoiceQuantity]),
FILTER(DimProduct,
DimProduct[NationalProductRollup] = "C" ||
DimProduct[NationalProductRollup] = "Other" ||
// NEED TO GROUP THESE TOGETHER SOMEHOW?
DimProduct[NationalProductRollup] = "A" &&
DimProduct[MainProductName] <> = "A1"
)
Perhaps:
// NEW MEASURE ATTEMPT: // Want it to grab the rest of the products not mentioned above Other Products Qty = CALCULATE( SUM(FactCPRGradesheetDetail[InvoiceQuantity]), FILTER(DimProduct, DimProduct[NationalProductRollup] <> "A1" && DimProduct[NationalProductRollup] <> "B" )??
4 Replies
- smpa01
Community Champion
You can try something like this
TRY= VAR 1= FILTER(DimProduct, DimProduct[NationalProductRollup] = "C" || DimProduct[NationalProductRollup] = "Other") VAR 2 = FILTER(DimProduct, DimProduct[NationalProductRollup] = "A" && DimProduct[MainProductName] <> = "A1" ) VAR 3 = Union (1,2) VAR 4 = Calculate (sumx(3,[InvoiceQuantity]) RETURN 4Anonymous
- Greg_Deckler
Community Champion
Perhaps:
// NEW MEASURE ATTEMPT: // Want it to grab the rest of the products not mentioned above Other Products Qty = CALCULATE( SUM(FactCPRGradesheetDetail[InvoiceQuantity]), FILTER(DimProduct, DimProduct[NationalProductRollup] <> "A1" && DimProduct[NationalProductRollup] <> "B" )??
- AnonymousNot applicable
Man I feel dumb, thank you
- Greg_Deckler
Community Champion
Sometimes it just takes another set of eyes!!