Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Good morning everyone,
I'm facing a problem in my power BI model and I'm looking for some help.
I have a customer table where I have all the customer ID with every transaction (order) they made during a period. So I have one line per transaction. I also have a column to know if is the first time a customer is purchasing with a boolean of Y/N.
The problem I'm facing is that if I want to know the NEW customers I have in my table, I just need to do a calculate with a filter = Y in the column where I notice if it's the first purchase; however, I want to know how many times a NEW customer bought me in a filter date selection.
For example, a customer buys for the first time in May 2023, and makes another purchase in July 2023. I want to know, that user is new and he did 2 purchases in that period. If I apply a filter in the page to know all the users news, I miss that second purchase that have a N in the first purchase column, and I don't want to lose it.
Any ideas?
Kind regards
Solved! Go to Solution.
@ATO23 , here is my suggested solution. Instead of a page-level filter, create the measures needed which do the filtering required.
I have used this set of test data:
CustomerID | orderID | period | order_value | new_customer |
C001 | O001 | 01/05/2023 | 1234 | Y |
C001 | O002 | 01/07/2023 | 2345 | N |
C002 | O003 | 01/05/2023 | 3274 | Y |
C003 | O004 | 01/07/2023 | 2874 | Y |
C004 | O005 | 01/07/2023 | 2774 | N |
Create a measure called [New Customers]:
New Customers = CALCULATE(DISTINCTCOUNT(YourTable[CustomerID]), YourTable[new_customer] = "Y")
Create a measure called [New Customer Orders]:
New Customer Orders =
VAR cust = SUMMARIZE(
FILTER(YourTable, YourTable[new_customer] = "Y"),
YourTable[CustomerID]
)
VAR orders = CALCULATE(
COUNTROWS(YourTable),
ALL(YourTable),
YourTable[CustomerID] IN cust
)
RETURN orders
Add a slicer to your report, using the [period] field.
Add a Card visual to your report, and drag in the [New Customers] measure.
Add a Card visual to your report, and drag in the [New Customer Orders] measure.
This gives me the following results when May-23 is selected.
There are 2 new customers in May-23 (C001 and C002).
Of those two new customers, there are 3 total orders in the table (O001, 002 and O003).
This gives me the following results when Jul-23 is selected.
There is 1 new customers in Jul-23 (C003).
Of that new customer, there is 1 total orders in the table (O004).
Hope this helps.
That works perfectly! Many thanks for your help!
@ATO23 , here is my suggested solution. Instead of a page-level filter, create the measures needed which do the filtering required.
I have used this set of test data:
CustomerID | orderID | period | order_value | new_customer |
C001 | O001 | 01/05/2023 | 1234 | Y |
C001 | O002 | 01/07/2023 | 2345 | N |
C002 | O003 | 01/05/2023 | 3274 | Y |
C003 | O004 | 01/07/2023 | 2874 | Y |
C004 | O005 | 01/07/2023 | 2774 | N |
Create a measure called [New Customers]:
New Customers = CALCULATE(DISTINCTCOUNT(YourTable[CustomerID]), YourTable[new_customer] = "Y")
Create a measure called [New Customer Orders]:
New Customer Orders =
VAR cust = SUMMARIZE(
FILTER(YourTable, YourTable[new_customer] = "Y"),
YourTable[CustomerID]
)
VAR orders = CALCULATE(
COUNTROWS(YourTable),
ALL(YourTable),
YourTable[CustomerID] IN cust
)
RETURN orders
Add a slicer to your report, using the [period] field.
Add a Card visual to your report, and drag in the [New Customers] measure.
Add a Card visual to your report, and drag in the [New Customer Orders] measure.
This gives me the following results when May-23 is selected.
There are 2 new customers in May-23 (C001 and C002).
Of those two new customers, there are 3 total orders in the table (O001, 002 and O003).
This gives me the following results when Jul-23 is selected.
There is 1 new customers in Jul-23 (C003).
Of that new customer, there is 1 total orders in the table (O004).
Hope this helps.
Hi @ATO23 check content on the link for start
https://www.daxpatterns.com/new-and-returning-customers/
Proud to be a Super User!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
71 | |
37 | |
21 | |
18 | |
15 |
User | Count |
---|---|
126 | |
32 | |
27 | |
24 | |
23 |