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 Any chance you can post your two sample source tables as text? 

 

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Oros's avatar
Oros
Post Prodigy
3 years ago

Hello Greg_Deckler ,

 

Thank you for your quick reply.  Here are the tables using the table tools.

 

PRODUCT TABLE

 

PRODUCTPRICING LEVELPRICE
Applebronze$15.00
Applesilver$10.00
Applegold$5.00
Applecustomer #777$3.00
Applecustomer #888$2.00
Bananabronze$5.00
Bananasilver$4.00
Bananagold$3.00

 

CUSTOMER TABLE

Customer #PRICING LEVEL
#123bronze
#456silver
#789bronze
#777bronze
#888gold

 

RESULTING TABLE

Customer #ProductPrice
#123Apple$15.00
#123Banana$5.00
#456Banana$4.00
#789Apple$15.00
#789Banana$5.00
#777Apple$3.00
#777Banana$5.00
#888Apple$2.00
#888Banana$3.00
  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    Oros PBIX is attached below signature. The Table formula is:

    Table = 
ADDCOLUMNS(
    GENERATE(DISTINCT('Customer'[Customer #]),DISTINCT('Product'[PRODUCT])),
    "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])
            )
)
    • Oros's avatar
      Oros
      Post Prodigy
      3 years ago

      Hi Greg_Deckler ,

       

      The formula seems to work on a smaller example data but I am getting the following error when the formula is applied to the actual data that contains a lot of records.

       

      Is there an alternative formula that will not strain the resources.  I ran it on Power Bi desktop that has plenty of resources and memory.

       

      "<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 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?