Force All Filter Context

[RyanNewportJP]

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! 

[v-jingzhan-msft]

Hi @RyanNewportJP 

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!

[RyanNewportJP]

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? 

[v-jingzhan-msft · Accepted solution]

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

[RyanNewportJP]

Thank you for the fix! I think this is the way to go. Next is figuring out how to call on the info in this table when looking up repeat/one-timers, but I'll go ahead and open a new thread on that when I have time. 

[v-jingzhan-msft]

You can try measures like below ones to count the customers. 

One-Time Customers = 
COUNTROWS(FILTER('Customers and LTV','Customers and LTV'[NumOrders] = 1))

Repeat Customers = 
COUNTROWS(FILTER('Customers and LTV','Customers and LTV'[NumOrders] > 1))

No. of Customers = COUNTROWS('Customers and LTV')

The logic is similar to the measures in my first reply. The difference is that the measures in this reply call on the info from a calculated table, but in my first reply, the measures call on the info from a variable table evaluated within the measure itself. 

Best Regards,
Jing

[bhanu_gautam]

@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)
)
)
)

[RyanNewportJP]

I tried that already and it didn't work, it's showing the same results with or without ALL(Table) added. 

[bhanu_gautam]

@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)
)
)
)

[RyanNewportJP]

Sorry, I was looking at the wrong dashboard. It now isn't showing any orders as new and all as repeat:

I tried chaing one-time customrs's operator to ==, but nothing changed...