Forum Discussion
Post ProdigyIf statement
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))) ) )
Post ProdigyHello 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 |
Community ChampionOros 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
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.