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)))
            )
)
Greg_Deckler's avatar
Greg_Deckler
Community Champion
3 years ago

Oros Few things to try. First, implement this as a table:

Table = GENERATE(DISTINCT('Customer'[Customer #]),DISTINCT('Product'[PRODUCT]))

If that is OK, then add this as a column:

Price =
            VAR __Customer = [Customer #]
            VAR __Product = [PRODUCT]
            VAR __PricingLevel = MAXX(FILTER('Customer',[Customer #] = __Customer),[PRICING LEVEL])
            VAR __Table = FILTER('Product',[PRODUCT] = __Product && CONTAINSSTRING([PRICING LEVEL],__Customer))
        RETURN
            IF(
                COUNTROWS(__Table)+0 = 0,
                MAXX(FILTER('Product',[PRODUCT] = __Product && [PRICING LEVEL] = __PricingLevel),[PRICE]),
                MAXX(__Table,[PRICE])
            )

If that doesn't work, then we'll either go down the Power Query route or reluctantly go down the CALCULATE route. How many records in your tables? 

Oros's avatar
Oros
Post Prodigy
3 years ago

Hi Greg_Deckler ,

 

In product table, I have at least 2000 products.  In customer table, I have at least 500 customers.  I will try the 2 step method that you suggested and I will keep you posted.  Thank you again.