Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Create a calculated column from two measures

Hello Community  -  I have these two measure, which work perfectly fine.  

 

However, I'm having a bit of challenge using these (or the logic within them) to create a calculated column that would essentially be a toggle between  New  or   Returning Customer.    So, if I wanted to see a column of all new customers, I could easily do so, or likewise if I wanted to see a column of returning customers.   

 

I obviusly need a calculated column to define, at the row level, who is returning, and who is new.   Just not sure how to utilize what I have so far to create this column.   

 

Returning Customers =

VAR _CustomerList = CALCULATETABLE(VALUES(SalesOrdersALL[CustNum_Cust_Seq_Orders]),USERELATIONSHIP('Dim_Date Table'[Date],SalesOrdersALL[Order Date]))

VAR _ReturningCustomers = (CALCULATETABLE(VALUES(SalesOrdersALL[CustNum_Cust_Seq_Orders]),USERELATIONSHIP('Dim_Date Table'[Date],SalesOrdersALL[Order Date]),

(FILTER(ALL('Dim_Date Table'[Date]),

'Dim_Date Table'[Date] > MIN('Dim_Date Table'[Date]) - [Churn Time Frame Value]

&& 'Dim_Date Table'[Date] < MIN('Dim_Date Table'[Date])))))

 

RETURN

COUNTROWS(INTERSECT(_CustomerList,_ReturningCustomers))

 

 

New Customers = //calculating which customers within any particular month have purchased but haven't done so for the last X number of days

VAR _CustomersThisMonth = CALCULATETABLE(VALUES(SalesOrdersALL[CustNum_Cust_Seq_Orders]),USERELATIONSHIP('Dim_Date Table'[Date],SalesOrdersALL[Order Date]))

VAR _PriorCustomers = CALCULATETABLE(VALUES(SalesOrdersALL[CustNum_Cust_Seq_Orders]),USERELATIONSHIP('Dim_Date Table'[Date],SalesOrdersALL[Order Date]),

FILTER(ALL('Dim_Date Table'[Date]),

'Dim_Date Table'[Date] > MIN('Dim_Date Table'[Date]) - [Churn Time Frame Value]

&& 'Dim_Date Table'[Date] < MIN('Dim_Date Table'[Date])))

VAR _NewCustomers = COUNTROWS(EXCEPT(_CustomersThisMonth,_PriorCustomers))

 

RETURN

_NewCustomers

Need Help

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    You cannot create columns from measures. These concepts (immutable vs mutable) are not reversible.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    lbendlin   Yes, that is clear.   My question is really how can I take the results of the above measures, and create a calculated column from their results.   In another words, a column that list whether a customer is a new or return customer  (based on the results of the measures).     

    • lbendlin's avatar
      lbendlin
      Super User
      4 years ago

      You cannot create columns from measures. Convert your measures to calculated columns, do your computations in Power Query, or do them further upstream.

    • v-zhangti's avatar
      v-zhangti
      Community Support
      4 years ago

      Hi, Anonymous 

       

      Can you provide a simple PBIX file for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures.

       

      Best Regards