Look up within Same Table based on Order Created Date

[SwatKat]

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
90001	1/13/2023	Email	520005
90002	1/23/2023	Form	520005
90003	3/14/2023	Online	520005
90004	5/16/2023	Email	520005
90005	1/13/2023	Email	520006
90006	1/23/2023	Form	520006
90007	3/14/2023	Online	520006
90008	5/16/2023	Form	520006
90009	4/17/2023	Email	520007
90010	5/16/2023	Form	520007
90011	6/17/2023	Form	520007

Expected Output

CustomerID	Orderered Online in Past
520005	Yes
520006	Yes
520007	No

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

[Martin_D · Accepted solution]

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

[alexschindler]

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]

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