Forum Discussion

Oros's avatar
Oros
Post Prodigy
3 years ago
Solved

If statement

Hello,    I have a product table and a customer table. (please see tables below)   The product table list the pricing level for each product.  The pricing level also uses customer code as a prici...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    Oros Updated PBIX attached. 

    Table2 = 
ADDCOLUMNS(
    GENERATE(DISTINCT('Customer'[Customer #]),DISTINCT('Product'[PRODUCT])),
    "Price",
            VAR __Customer = [Customer #]
            VAR __Product = [PRODUCT]
            VAR __PricingLevel = CALCULATE(MAX('Customer'[PRICING LEVEL]), 'Customer'[Customer #] = __Customer)
            VAR __Table = CALCULATE(COUNTROWS('Product'),FILTER('Product', [PRODUCT] = __Product && CONTAINSSTRING([PRICING LEVEL],__Customer))) + 0
        RETURN
            IF(
                __Table = 0,
                CALCULATE(MAX('Product'[PRICE]), FILTER('Product', [PRODUCT] = __Product && [PRICING LEVEL] = __PricingLevel)),
                CALCULATE(MAX('Product'[PRICE]),FILTER('Product', [PRODUCT] = __Product && CONTAINSSTRING([PRICING LEVEL],__Customer)))
            )
)
Oros's avatar
Oros
Post Prodigy
3 years ago

Hello Greg_Deckler ,

 

Implementing a table, then adding the column also resulted in not enough memory message.

 

What would be the Power Query or CALCULATE route that you would like recommend? Thanks.

"<ccon>There's not enough memory to complete this operation. Please try again later when there may be more memory available.</ccon>"

 

Greg_Deckler's avatar
Greg_Deckler
Community Champion
3 years ago

Oros Updated PBIX attached. 

Table2 = 
ADDCOLUMNS(
    GENERATE(DISTINCT('Customer'[Customer #]),DISTINCT('Product'[PRODUCT])),
    "Price",
            VAR __Customer = [Customer #]
            VAR __Product = [PRODUCT]
            VAR __PricingLevel = CALCULATE(MAX('Customer'[PRICING LEVEL]), 'Customer'[Customer #] = __Customer)
            VAR __Table = CALCULATE(COUNTROWS('Product'),FILTER('Product', [PRODUCT] = __Product && CONTAINSSTRING([PRICING LEVEL],__Customer))) + 0
        RETURN
            IF(
                __Table = 0,
                CALCULATE(MAX('Product'[PRICE]), FILTER('Product', [PRODUCT] = __Product && [PRICING LEVEL] = __PricingLevel)),
                CALCULATE(MAX('Product'[PRICE]),FILTER('Product', [PRODUCT] = __Product && CONTAINSSTRING([PRICING LEVEL],__Customer)))
            )
)