Forum Discussion

SwatKat's avatar
SwatKat
Frequent Visitor
2 years ago
Solved

Look up within Same Table based on Order Created Date

Hi,

 

I have a Orders  table as below in my Power BI file . I need to show list of customers who have Ordered Online in the past. This will be used in a table visual. I tried Look Up but that is not working.  I am unable to understand how to achieve this in M Query either. Any inputs are highly appreciated.  I have tried to created measure too , but that did not work as well ðŸ˜ž 

 

OrderID       OrderCreatedDate          Source       CustomerID      
900011/13/2023Email520005
900021/23/2023Form520005
900033/14/2023Online520005
900045/16/2023Email520005
900051/13/2023Email520006
900061/23/2023Form520006
900073/14/2023Online520006
900085/16/2023Form520006
900094/17/2023Email520007
900105/16/2023Form520007
900116/17/2023Form520007

 

 

 

Expected Output

 

CustomerID       Orderered Online in Past     
520005Yes
520006Yes
520007No

 

Tagging Top solution others since this is something i need to close quickly Martin_D , lbendlin , parry2k , Ashish_Mathur , amitchandak . I hope you understand my tight timeline

Need Help
  • Martin_D's avatar
    Martin_D
    2 years ago

    Hi SwatKat ,

    I'd recommend to solve this problem in a measure, not in M Query. This DAX measure

     

     

    Has ordered online = IF ( "Online" in VALUES ('Orders'[Source] ), "Yes", "No" )

     

     

    will give you this result:

    Kind regards,

    Martin

3 Replies

  • alexschindler's avatar
    alexschindler
    Helper I
    2 years ago

    Hi SwatKat

    Can you not use some measure for this?

    Something along the lines of ...

    ordered_online =
VAR ordered_online_in_past = CALCULATE(DISTINCTCOUNT(order_table[OrderID]), FILTER('orders_table', orders_table[Source] = "Online"))
RETURN
IF(ordered_online_in_past > 0,
"yes",
"no"
)

    Good luck

    Alex

    • SwatKat's avatar
      SwatKat
      Frequent Visitor
      2 years ago

      Thanks Alex for your solution. I have tried solution provided by Martin_D  and it worked. It looks like your solution will work pretty fine too. 

       

      Thanks for the prompt responses

  • Martin_D's avatar
    Martin_D
    Solution Sage
    2 years ago

    Hi SwatKat ,

    I'd recommend to solve this problem in a measure, not in M Query. This DAX measure

     

     

    Has ordered online = IF ( "Online" in VALUES ('Orders'[Source] ), "Yes", "No" )

     

     

    will give you this result:

    Kind regards,

    Martin