cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Post Prodigy
Post Prodigy

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

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 IDYearItem Purchased
1234562014Apples
1234562015Apples
1234562016Apples
1234562017Apples
1234562018Oranges
7891012014Grapes
7891012015Grapes
7891012016Grapes
7891012017Grapes
7891012018Peaches
7891012019Kiwi
2424242015Bananas
2424242016Bananas
2424242017Bananas
2424242018Bananas

 

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!!!

 

 

1 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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

7 REPLIES 7
Super User III
Super User III

Hi,

 

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

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@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.

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Super User III
Super User III

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   www.daxformatter.com

 

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.

@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?

   

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

 

Thanks again.

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors