Forum Discussion

Oros's avatar
Oros
Icon for Post Prodigy rankPost 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
Icon for Community Champion rankCommunity 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
    Icon for Post Prodigy rankPost 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
      Icon for Community Champion rankCommunity 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
        Icon for Post Prodigy rankPost 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>"