Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 | 520005 | |
90002 | 1/23/2023 | Form | 520005 |
90003 | 3/14/2023 | Online | 520005 |
90004 | 5/16/2023 | 520005 | |
90005 | 1/13/2023 | 520006 | |
90006 | 1/23/2023 | Form | 520006 |
90007 | 3/14/2023 | Online | 520006 |
90008 | 5/16/2023 | Form | 520006 |
90009 | 4/17/2023 | 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
Solved! Go to 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
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
1 | |
1 | |
1 | |
1 |
User | Count |
---|---|
26 | |
3 | |
2 | |
2 | |
2 |