Forum Discussion
Force All Filter Context
Hi all,
I found some DAX on the web that calculates new vs. repeat users. However, the DAX only works for users who bought within the sliced timeframe, not the entire dataset.
The code is below; I'd like help changing it to check if that is a single/repeat buyer at any time.
Repeat Customers =
countrows(filter(distinct(Table[CustomerID]),calculate(distinctcount(Table[OrderNumber]) > 1)))↑ Gives ttl number of repeaters in filtered range
One-Time Customers =
countrows(filter(distinct(Table[CustomerID]),calculate(distinctcount(Table[OrderNumber])= 1)))↑Gives ttl number of one-time customers in filtered range
I tried adding ALL(Table) to the CALCULATE functions, but I think the problem is that the DISTINCT CustomerIDs are getting filtered, and I'm not sure how to do that...
Any help would be appreciated!
If you want the static result not filtered by slicers/filters, the idea to create a calculated table to summarize the number in advance is doable. And this can improve the performance of measures because they can count easily from this new table.
But your table code is not correct. You could try below code instead.
Customers and LTV = Summarize('Table','Table'[CustomerID],"LTV",SUM('Table'[ProductTotal]),"NumOrders",DISTINCTCOUNT('Table'[OrderNumber]))Best Regards,
Jing
9 Replies
RyanNewportJP You can achieve this by using the ALL function to remove any filters applied to the Table in the CALCULATE function.
DAX
Repeat Customers =
COUNTROWS(
FILTER(
DISTINCT(Table[CustomerID]),
CALCULATE(
DISTINCTCOUNT(Table[OrderNumber]) > 1,
ALL(Table)
)
)
)One-Time Customers =
COUNTROWS(
FILTER(
DISTINCT(Table[CustomerID]),
CALCULATE(
DISTINCTCOUNT(Table[OrderNumber]) = 1,
ALL(Table)
)
)
)I tried that already and it didn't work, it's showing the same results with or without ALL(Table) added.
RyanNewportJP , Try using remove filter than, Can you share sample data for testing
dax
Repeat Customers =
COUNTROWS(
FILTER(
DISTINCT(Table[CustomerID]),
CALCULATE(
DISTINCTCOUNT(Table[OrderNumber]) > 1,
REMOVEFILTERS(Table)
)
)
)One-Time Customers =
COUNTROWS(
FILTER(
DISTINCT(Table[CustomerID]),
CALCULATE(
DISTINCTCOUNT(Table[OrderNumber]) = 1,
REMOVEFILTERS(Table)
)
)
)
Please try these measures:
One-Time Customers = var summarizeTable = SUMMARIZE(ALL('Table'),'Table'[CustomerID],"order_count",DISTINCTCOUNT('Table'[OrderNumber])) return COUNTROWS(FILTER(summarizeTable,[order_count]=1))Repeat Customers = var summarizeTable = SUMMARIZE(ALL('Table'),'Table'[CustomerID],"order_count",DISTINCTCOUNT('Table'[OrderNumber])) return COUNTROWS(FILTER(summarizeTable,[order_count]>1))You can also combine the lines into one line like below (I take the second measure for example)
Repeat Customers = COUNTROWS(FILTER(SUMMARIZE(ALL('Table'),'Table'[CustomerID],"order_count",DISTINCTCOUNT('Table'[OrderNumber])),[order_count]>1))Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!Hi! Sorry for the late reply.
That made static number for one-time/repeaters that doesn't change based on what's in the slicers/other filters.
I guess I need a static table of all one-time and repeat customers that then I can call back to?
I tried to do that with the below code that creates a table the CustomerID and a count of OrderNumber, but some of the OrderNumber counts were absolutely nuts (like, thousands of orders).
Customers and LTV = Summarize(Table,Table[CustomerID],"LTV",SUMx(Table,Table[ProductTotal]),"NumOrders",SUMX(Table,((DISTINCTCOUNT(Table[OrderNumber]])))))Is there an issue in the above code?
If you want the static result not filtered by slicers/filters, the idea to create a calculated table to summarize the number in advance is doable. And this can improve the performance of measures because they can count easily from this new table.
But your table code is not correct. You could try below code instead.
Customers and LTV = Summarize('Table','Table'[CustomerID],"LTV",SUM('Table'[ProductTotal]),"NumOrders",DISTINCTCOUNT('Table'[OrderNumber]))Best Regards,
Jing
