Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Lookup Value

Hi

We need to add a column to the database where we use teh lookup functions.

Eg:  in 1 column i have the numbers 1,2,3,4, upto 90.

We need to run a formula using lookup where the number is between 1-30 then it shows 1 Month. If its 31 to 60 then 2 Month and if its 61 to 90 then 3 Months.,

Need Help.

 

Fasial

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

    Please have a try.

    Create a column.

    Column = IF('Table'[value]>=1&&'Table'[value]<=30,"1",IF('Table'[value]>=31&&'Table'[value]<=60,"2","3"))

    Or try this one.

    Column 2 = SWITCH(TRUE(),'Table'[value]>=1&&'Table'[value]<=30,1,'Table'[value]>=31&&'Table'[value]<=60,2,3)

     

    Best Regards

    Community Support Team _ Polly

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , a new column

     

    Month = Switch( True() ,

    [Column] <= 30 ,1 ,

    [Column] <= 60 ,2 ,

    3)

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      OK. But in this case if the number is 15, will it be accounted in 1 or 2. Because 15 is less than 30 and its also less than 60.

      In excel we use the look up function with a table as follows:

      1      30   1 Month

      31    60.   2 Month

      61.   999.  More than 2 Months

       

      Then on this we run look up formula. similarly cant we do here in power BI?

       

      Faisal

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Amit

    Thanks for your Reply

    In the above formula, if we need to add like Less than or equal 30 then 1. The greater than 30 and less than or equal 60 then 2 and rest 3.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

    Please have a try.

    Create a column.

    Column = IF('Table'[value]>=1&&'Table'[value]<=30,"1",IF('Table'[value]>=31&&'Table'[value]<=60,"2","3"))

    Or try this one.

    Column 2 = SWITCH(TRUE(),'Table'[value]>=1&&'Table'[value]<=30,1,'Table'[value]>=31&&'Table'[value]<=60,2,3)

     

    Best Regards

    Community Support Team _ Polly

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.