Forum Discussion

AliceA's avatar
AliceA
Regular Visitor
3 years ago
Solved

New column for total based on another table

Hi all, I think this should be obvious, but I can't figure out. Your help would be appreciated.   So, I have this table called Customers. One line per Customer.   I have a second table called Pu...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi AliceA ,

     

    I tried to reproduce it.

    And you can create a calculated column in Customers table to count the number of purchases.

    Count = CALCULATE(COUNT(Purchases[Purchase ID]),FILTER('Purchases',[Customer ID]=EARLIER(Customers[Customer ID])))

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

olgad's avatar
olgad
Resident Rockstar
3 years ago

Hi, you can create a table

SUMMARIZE(Purchases, 'Purchases'[CustomerID], "Number of Purchases", Count( 'Purchases'[CustomerID]))
Then on your customer table create a column
Column=LOOKUPVALUE('Table'[Number of Purchases], 'Table'[CustomerID],'Customer'[CustomerID])
AliceA's avatar
AliceA
Regular Visitor
3 years ago

Hi Olga,

Thank you, but it doesn't look like the newly created table will be influenced by the other columns in the Purchases table... I really need this total to change based on them (such as the date).

Would you have a solution for this issue, please?

Alice

  • olgad's avatar
    olgad
    Resident Rockstar
    3 years ago

    Apologies Alice, misread your last sentence as "you dont want the table to be affected". 

    There is no such thing as a dynamic calcualted column in this tool. So let us clarify a couple of questions then,  why dont you have a separate date table? What are the fileds you want to use from purchase table to filter the Customer table? Only the date or something else?
    We could create 2 measures first the count then classification based on the purchases table.

    If you give a bit more detail how you intend to display it in the end and how it shall function, I can figure how to help you.