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

 

 

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)))
            )
)

8 Replies

  • 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])
            )
)