cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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 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

1 ACCEPTED SOLUTION
Super User

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 3
Super User

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

Frequent Visitor

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

Frequent Visitor

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors