Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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's avatar
    smpa01
    Icon for Community Champion rankCommunity 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 4
    
    

     

    Anonymous  

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity 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"
    		)

     

    ??

    • Anonymous's avatar
      Anonymous
      Not applicable

      Man I feel dumb, thank you