Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all, I am a beginer in Power BI, i have strugggling with a mearsure problem. For example,
Table 1
Customer_ID | date | Register | Referral Link | First Visit | First Click into bag | First_Purchase | Online shop Channel |
26 | 1/4/2011 | 0 | 0 | 0 | 0 | 1 | 7 |
26 | 11/7/2012 | 0 | 0 | 0 | 1 | 0 | 44 |
26 | 1/12/2013 | 0 | 0 | 0 | 0 | 1 | 17 |
26 | 14/3/2014 | 0 | 0 | 1 | 0 | 0 | 6 |
I want to calculate the first_Purchase(FP) for the Customer_ID by ignoring the online shop channel (obviously customer ID 26 his first_purhcase(FP) would be 1/4/2011 by ignoring the shop Channel
I only got the right answer once i created a new table by the following
step 1.
New Table = CALCULATETABLE(SUMMARIZE(table 1,table 1[Customer_ID], "Mdate",CALCULATE(MIN(Table 1[date]),Table 1[First_Purchase]=1)), Table 1[First_Purchase]=1)
step 2.
Nr of customer with at least 1 first purchase = count('New table'[Mdate])
The answer will be 1 (as i only want to get the min date of first purchase)
However, i used same logic to create the measure in table
e.g distinctcount(table 1[customer ID],first_purchase =1). or countrow(summarize(tabl 1..))
the result i get is larger than the New Table answer. it seem this in table 1 counting those record with different shop channels therefore this customer ID would be show again on Dec 2013 (What i want is just show once in april 2011).
I don't want to change the table structure it is because i have to do further analysis in all customer's first visit and click into bag etc..
Thanks and appreciate with your help
Hi @Yikwun831, I am not sure that I follow all of this 100% but if you want a visual where you have the Customer_ID and min date of first purchase you should be able to create a measure like this:
Measure = VAR __customerID = MAX('Table 1'[Customer_ID]) VAR __table = FILTER(ALL('Table 1'),[Customer_ID] = __cucstomerID && [First_Purchase] = 1 VAR __date = MINX(__table,[date]) RETURN __date
Put that measure into a visual with your Customer_ID and see if that is what you are looking to achieve.
Thanks a lot Deckler,
In this " __cucstomerID && [First_Purchase] = 1"
i don't have the selection of [first_purchase] (this column data i generated by query from database), so at the memont I still cannot test the measure yet.
In addition, I have loads of data for example customer ID 27,28 etc. Their each first action (first view, first purchase...) has been fully recorded and generated a row for each first action in different online shop channels.
So ultimately, i would like to show their first purchase by year month.
for example, Customer ID 26 only show in april 2011
but not in both april 2011 and Dec 2013 (otherwise it will be duplicate my calculation for new customer because i am defining the totally new customer instead of a exist customer who having a new purchase in new channel but they have already purchase in another channels
Thank you so much Deckler!
Hi @Yikwun831 ,
Here is my sample data.
Then I created a new measure to get date of first purchase.
Measure = CALCULATE ( MIN ( test[date] ), ALLEXCEPT ( test, test[ID] ), test[first purchase] = 1 )
Here is the result. Does this suit your needs?
Yes, this measure is great! however, I want to count their first purchase instead of showing
For this example, i want is ID_26 will be only showed in April 2011, 27 only in May 2013 and 28 only May 2015.
It only happens in the month that has its inital first purchase (even they have another first purchase in other channel)
secondly, i want to visualize that measure no matter in matrix form / stacked chart.
I tried many ways and method, but finally it doesn't work.
the graph will show all the first purchase in different channles.
thanks a lot mate! it would be great if i have your help
Hi @Yikwun831 ,
I am still a little ambiguous about your needs. Would you like to provide a sample result you want?
And you can download my test file, I created two kinds of matrix form in it.
Thanks for your help. I am really appreciate that.
I have downloaded your sample and read through it, it was really good! it helps me to understand the DAX and logic behind this.
The matrix with measure 1 is the thing i want exactly (but the number of total of row&column seems all 0)
Furthermore, i would like to have the stacked column chart with x axis = year month (Date) and y-axis = sum of the no. of first purchase in that month (the "first purchase" has been defined in previously)
then using the same dax to create the new column like your table column 1 and 2, but it doesn't work in my tabke.
Hence,I am just wondering that might due to the edit queries problem? ( as I extract the data from database by SQL and then consolidate the data)
Basically, my first step is extracting data (the first view, first purchase etc all is timely data) by SQL, then i unpivoted columns and used conditional cloumn to create new columns for first purchase, etc.
Customer ID | Online shop Channel | Reg | first view | first click | first purchase |
26 | 1 | 14/2/2011 | |||
26 | 2 | 14/5/2010 | |||
26 | 9 | 5/6/2011 | |||
26 | 8 | 18/12/2014 | |||
26 | 13 | 18/12/2010 | |||
27 | 3 | 9/9/2013 | |||
27 | 4 | 1/8/2013 | |||
28 | 5 | 1/1/2019 | |||
28 | 6 | 14/4/2018 |
then i unpivoted columns
Customer ID | Online shop Channel | Type | Date |
26 | 1 | Reg | 14/2/2011 |
26 | 2 | first view | 14/5/2010 |
26 | 9 | first purchase | 5/6/2011 |
26 | 8 | first purchase | 18/12/2014 |
26 | 13 | first click | 18/12/2010 |
27 | 3 | first purchase | 9/9/2013 |
27 | 4 | first click | 1/8/2013 |
28 | 5 | reg | 1/1/2019 |
28 | 6 | first view | 14/4/2018 |
then conditional column
Customer ID | Date | Online shop Channel | Reg | first view | first click | first purchase |
26 | 14/2/2011 | 1 | 1 | 0 | 0 | 0 |
26 | 14/5/2010 | 2 | 0 | 1 | 0 | 0 |
26 | 5/6/2011 | 9 | 0 | 0 | 0 | 1 |
26 | 18/12/2014 | 8 | 0 | 0 | 0 | 1 |
26 | 18/12/2010 | 13 | 0 | 0 | 1 | 0 |
27 | 9/9/2013 | 3 | 0 | 0 | 0 | 1 |
27 | 1/8/2013 | 4 | 0 | 0 | 1 | 0 |
28 | 1/1/2019 | 5 | 1 | 0 | 0 | 0 |
28 | 14/4/2018 | 6 | 0 | 1 | 0 | 0 |
after all, i started working with those data like creating the measure and visualize the date
is there any mistake happen so i cannot create the measure and column like yours?
Thanks
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |