Forum Discussion

RyanNewportJP's avatar
RyanNewportJP
Frequent Visitor
1 year ago
Solved

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 be...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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's avatar
RyanNewportJP
Frequent Visitor
1 year ago

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? 

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
    RyanNewportJP
    Frequent Visitor
    1 year ago

    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. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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