Comparing Row Differences in a single dataset - DAX or Operation Help Needed!!

[irnm8dn]

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?

• 2 of my 3 customers had a change in the product they purchased (based on True/False or 1/0)
• 1 of my 3 customers did not change the product purchased

How many times did I have customer that changes 3 times, 2 times, 0 times, etc.

• 1 of my customers changed item purchased 3 times (Sum count of changes) 
• 1 of my customers changed item purchased 2 times (Sum count of changes) 
• 1 of my customers changed item purchased 0 times (Sume count of changes)

Thanks in advance!!!

[Ashish_Mathur]

Hi,

Will this work?  You may dowload my PBI file from here.

Hope this helps.

[irnm8dn]

@Ashish_Mathur

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?

Times Product changed = if(HASONEVALUE(Data[Customer ID]),SUMX(SUMMARIZE(VALUES(Data[Customer ID]),Data[Customer ID],"ABCD",DISTINCTCOUNT(Data[Item Purchased])-1),[ABCD]),BLANK())

Particularly the "ABCD" part as well?

Thanks.

[Ashish_Mathur · Accepted 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.

[AlB]

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.

• 1 of my customers changed item purchased 3 times (Sum count of changes) 
• 1 of my customers changed item purchased 2 times (Sum count of changes) 
• 1 of my customers changed item purchased 0 times (Sume count of changes)

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.

Code formatted with  

[irnm8dn]

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.

[AlB]

@irnm8dn

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?

[irnm8dn]

Yep, that's how I was thinking of it as well.  Just lack your DAX prowess.

Thanks again.