Forum Discussion

rcb0325's avatar
rcb0325
Helper I
5 years ago
Solved

Converting If/else statements from Excel

I have a table that I am trying to figure out the DAX input for. The formula in excel was: 

 

=if([Invoice Account]="112541",.11,IF([Customer Rebate Group]="TDG",.07,IF(OR([Invoice Account]="139871",[Invoice Account]="131324"),.08,.07)))

 

In Power BI, the table has a column of customer numbers, that I need to create a measure for to assign the values above to, so I can bring those in as another column. Can someone help me convert the excel formula? 

Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    5 years ago

    Hi rcb0325 ,


    You could modify measure by the following formula: 

    Rebate Multiplier =
IF (
    MAX ( [BillToCustomerCustomerNumber] ) = "112541",
    .11,
    IF (
        MAX ( [BillToCustomerCustomerRebateGroup] ) = "TDG",
        .07,
        IF (
            MAX ( [BillToCustomerCustomerNumber] ) = "139871"
                || MAX ( [BillToCustomerCustomerNumber] ) = "131324",
            .08,
            .07)))

    The final output is shown below:

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Hi,

    The calculated column formula should remain the same in DAX as well.  Just replace Invoice Account, Customer Rebate Group and Invoice Account with customer numbers.

    Hope this helps.

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    5 years ago

    Hi rcb0325 ,


    You could modify measure by the following formula: 

    Rebate Multiplier =
IF (
    MAX ( [BillToCustomerCustomerNumber] ) = "112541",
    .11,
    IF (
        MAX ( [BillToCustomerCustomerRebateGroup] ) = "TDG",
        .07,
        IF (
            MAX ( [BillToCustomerCustomerNumber] ) = "139871"
                || MAX ( [BillToCustomerCustomerNumber] ) = "131324",
            .08,
            .07)))

    The final output is shown below:

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • rcb0325's avatar
    rcb0325
    Helper I
    5 years ago

    When I type out the DAX, and replace the fields with what they should be, it is not allowing me to use them. (highlighted below). 

     

    Is there a certain format that the field has to be in? 

     

    =IF([BillToCustomerCustomerNumber]="112541",.11,IF([BillToCustomerCustomerRebateGroup]="TDG",.07,IF(OR([BillToCustomerCustomerNumber]="139871",[BillToCustomerCustomerNumber]="131324"),.08,.07)))

     

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      5 years ago

      Hi,

      It should be written as a calculated column formula (not as a measure).  Also, the best practise is to precede the column name with the table name.