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.
I have a single dataset that shows a date stamped transaction by customer ID. I am trying to determine if over time, what the customer buys, has changed. For instance, in the sample data set below, you can visually see that Customer IDs 123456 and 789101 both had a change in what they purchased, Customer ID 242424 did not.
I am looking for a DAX statement, or suggested opertation, that will allow me to compare what was purchased by customer ID, and wherer it has never changed "True" or if it has "False".
Additionally, if I could count the number of changes (by customer) it would be ideal.
Customer ID | Year | Item Purchased |
123456 | 2014 | Apples |
123456 | 2015 | Apples |
123456 | 2016 | Apples |
123456 | 2017 | Apples |
123456 | 2018 | Oranges |
789101 | 2014 | Grapes |
789101 | 2015 | Grapes |
789101 | 2016 | Grapes |
789101 | 2017 | Grapes |
789101 | 2018 | Peaches |
789101 | 2019 | Kiwi |
242424 | 2015 | Bananas |
242424 | 2016 | Bananas |
242424 | 2017 | Bananas |
242424 | 2018 | Bananas |
In closing, the summary could answer the following:
How many customers did or did not change their purchse year-to-year?
How many times did I have customer that changes 3 times, 2 times, 0 times, etc.
Thanks in advance!!!
Solved! Go to Solution.
Hi,
You are welcome. The VALUES(Data[Customer ID]) portion created a unique list of all Customer ID's. Since the VALUES() function will return a single column Table, that is the only column you can Group By (the second input). The third input is a title of the summarisation column that you want to create (you may type any descriptive heading within the double quotes). DISTINCTCOUNT() counts the distinct entries for each customers. The SUMX() functions adds up the figures appearing in the ABCD column.
Hi,
Will this work? You may dowload my PBI file from here.
Hope this helps.
Thank you so much. I am going to apply this to my actual dataset and try it.
Can you breakdown the functions from the DAX statement so I can appreciate the operation and what it's doing?
Hi,
You are welcome. The VALUES(Data[Customer ID]) portion created a unique list of all Customer ID's. Since the VALUES() function will return a single column Table, that is the only column you can Group By (the second input). The third input is a title of the summarisation column that you want to create (you may type any descriptive heading within the double quotes). DISTINCTCOUNT() counts the distinct entries for each customers. The SUMX() functions adds up the figures appearing in the ABCD column.
Hi @irnm8dn
First of all, I believe there's a bit of an inconsistency in the expected results you mention. I quote:
How many times did I have customer that changes 3 times, 2 times, 0 times, etc.
Why is the last one not 1 change? It should be, following the same reasoning as applied to the others.
The code below considers that
- ID 123456 has 1 change (apple to orange)
- ID 789101 has 2 changes (Grapes to peaches, peaches to kiwis)
- ID 24424 has 0 changes (all bananas, so no change)
See the inconsistency i was referring to? If you want to consider that all bananas is 1 change, you can just remove the -1 at the end of the measure below.
So try this:
1. Place Table1[Customer ID] in the rows of a matrix visual
2. Create this measure and place it in values of the matrix
NumberOfChanges = COUNTROWS ( FILTER ( Table1; VAR _PreviousYear = CALCULATE ( MAX ( Table1[Year] ); Table1[Year] < EARLIER ( Table1[Year] ) ) VAR _PreviousItemPurchased = LOOKUPVALUE ( Table1[Item Purchased]; Table1[Customer ID]; Table1[Customer ID]; Table1[Year]; _PreviousYear ) RETURN Table1[Item Purchased] <> _PreviousItemPurchased ) ) - 1
This will give you the number of changes per customer ID. From there you can do further calculations.
I see your point. Thank you.
How would you recommend a binary Change or No Change, or would your suggestion be to simply to base on your statement above as "0" or ">0"
Thanks.
Maybe there are other factors derived from your requirements that would play a role here. With the info I have, I would just use the measure above to determine whether there has been any change. Another measure along the lines of:
Changes? = IF ( [NumberOfChanges] > 0, "Change", "No change" )
Don't you think so?
Yep, that's how I was thinking of it as well. Just lack your DAX prowess.
Thanks again.
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 |
---|---|
107 | |
98 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |