Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

first order date/copy column from multiple tables

Dear Mates,

I have two tables order and user, both of these tables have a date column.The date column in order table specifies the order date and the one in user table specifies the date when user is added.I created a master calendar with the min and max of date field from user table as it has more data  and linked to both these tables.Now I need to find out new users added as well as count of user’s first order.I was able to find out new customers added, but couldn’t get the latter .Order table has order id ,user id, orderdate whereas user table has userid, userdate added along with other fields.my queries are listed below:

1.Is it possible to copy the column of user date added to order table so that I can check or is there any other options available.

2.Is the master calendar required?If not how to deal with missing dates?

Thank you for your time and pls advice as necessary

Regards

Rishi

Need Help
Tips and Tricks

2 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi Anonymous,

     

    Question 1

    If you have created a one to many relationship between user table and order table, you can added a calculated column in order table using this formula.

    user added date=related(user[user date])

     

    If you didn't create any relationship between these tables, you can use a LookUpValue function in order table so that it can reference to the user added date column.

    user added date = LOOKUPVALUE(user[user date],user[user id],'order'[user id]) 

     

    Question 2

    If above solutions can meet your requirement, the master calendar is not required. By the way, what do you mean deal with missing dates? And I am not clear about your second requirement that 'find out count of user’s first order'. Do you want to count the number of each user when they first give an order? If so, how about trying to create a measure like this: 

    Count Fisrt order= DISTINCTCOUNT('order'[user id])

     

    If I have something misunderstood, please elaborate your sceond requirement with some sample data and please give an example of your desired output.

     

    Regards,
    Yuliana Gu

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Dear v-yulgu-msft

      Thanks for your prompt reply.But I am facing some issues.I could create only many to one relation between order and user.I created a filter for year and month from order dates fields.The issue is that if I select the year and month the user dates added is not changing according to the filter.Can you please guide me on the same