Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

New Column with IF Logic and Excel Syntax

How can I create and add a new column in Power BI to mimick the excel formulas below? 

=IF(NUMBERVALUE(C2)<=250,"0-250",IF(NUMBERVALUE(C2)<=500,"251-500",IF(NUMBERVALUE(C2)<=750,"501-750",IF(NUMBERVALUE(C2)<=1000,"751-1000",IF(NUMBERVALUE(C2)<=1250,"1001-1250",IF(C2="2000","2000",IF(C2="9000","9000",LEFT(C2,4))))))))

 

 

IndexPhasefirst fourSection
10001-000000010-250
20002-000000020-250
30003-000000030-250
40004-000000040-250
50004-100000040-250
60005-000000050-250
70005-100000050-250
22469000-152090009000
22479000-153090009000
22489000-154090009000
22519999-900099999999
22529999-996099999999
22539999-997099999999
22549999-999999999999

 

Need Help
Show and Tell
Tips and Tricks
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    For First Four, use following DAX formula and change the column to Whole Number

     

    First Four = LEFT('Table'[Phase],4)

     

    For Section, use following DAX formula

     

           SWITCH(
                TRUE(),
                'Table'[First Four]<=250,"0-250",
                'Table'[First Four]<=500,"251-500",
                'Table'[First Four]<=750,"501-750",
                'Table'[First Four]<=1000,"751-1000",
                'Table'[First Four]<=1250,"1001-1250",
                'Table'[First Four]=2000,"2000",
                'Table'[First Four]=9000,"9000",
                LEFT('Table'[First Four],4)
            )

     

1 Reply

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    For First Four, use following DAX formula and change the column to Whole Number

     

    First Four = LEFT('Table'[Phase],4)

     

    For Section, use following DAX formula

     

           SWITCH(
                TRUE(),
                'Table'[First Four]<=250,"0-250",
                'Table'[First Four]<=500,"251-500",
                'Table'[First Four]<=750,"501-750",
                'Table'[First Four]<=1000,"751-1000",
                'Table'[First Four]<=1250,"1001-1250",
                'Table'[First Four]=2000,"2000",
                'Table'[First Four]=9000,"9000",
                LEFT('Table'[First Four],4)
            )