If statement

[Oros]

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 pricing level.  

The customer table has a pricing level assigned to each customer.

By default, the customer pricing is based on the assigned PRICING LEVEL.  However, IF the product has a specific PRICING LEVEL for a specific customer code, then the CUSTOMER CODE pricing level must apply or must show in the RESULTING TABLE.

What would be the correct IF STATEMENT measure or column to apply in this situation?  Thanks.

[Greg_Deckler]

@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]

Hello @Greg_Deckler ,

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

PRODUCT TABLE

PRODUCT	PRICING LEVEL	PRICE
Apple	bronze	$15.00
Apple	silver	$10.00
Apple	gold	$5.00
Apple	customer #777	$3.00
Apple	customer #888	$2.00
Banana	bronze	$5.00
Banana	silver	$4.00
Banana	gold	$3.00

CUSTOMER TABLE

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

RESULTING TABLE

Customer #	Product	Price
#123	Apple	$15.00
#123	Banana	$5.00
#456	Banana	$4.00
#789	Apple	$15.00
#789	Banana	$5.00
#777	Apple	$3.00
#777	Banana	$5.00
#888	Apple	$2.00
#888	Banana	$3.00

[Greg_Deckler]

@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]

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]

@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]

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 · Accepted solution]

@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]

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.