Reporting only the latest customer reaction in a given time window/topic and reporting change over t

[junglejimsvet]

Hi All,

I'm guessing I need to use some combination of Summarize and Group but don't yet have the skills and was hoping that someone could help me:)  Example excel file here customer shift example.xlsx  (apologies it won't let me upload directly to the forum)

Part 1:  

• Customers are seen multiple times on multiple topics over time and we record a "Reaction" to the topic
• I want to report the current landscape of customers latest reactions against the topics (so if Jim was seen 3 times on topic X, I only want to count the latest dated "Reaction" in the table
• So my question is what dax do I use to take the reaction at the max date for a given customer and topic

		# Customers
Parent Topic	Child Topic	Negative	Neutral	Positive
Politics and Economy	Politics	0	2	2
Politics and Economy	Economy	1	1	1
Weather and Football	Weather	0	1	1
Weather and Football	Football	1	1	0

Part 2:  We also want to assess the average shift in reaction for a given topic over time (so if a customer shifts from 1 to 3 in reaction there is a shift of 2, we then average that with all of the other customers who have been seen more than once)

		# Customers
Parent Topic	Child Topic	Average Shift
Politics and Economy	Politics	1.5	Jim moved from 1 to 3, Milly Moved from 2 to 3… avg = (2+1)/2 = 1.5
Politics and Economy	Economy	1	Only Alex seen multiple times from 1 to 2
Weather and Football	Weather	1	Only Alex seen multiple times from 1 to 3
Weather and Football	Football	N/A	No one see more than once

Question:  how do I write the dax to pull the earliest and latest reactions for a given customer / topic combination

Many thanks,

Jim

Below is the dummy data as shown on the link (in case link does not work)

Call NameCountryCustomer IDCustomer NameDateParent TopicChild TopicReactionReaction as numberPriority Conversation

1	UK	4	Jim	01-Jan	Politics and Economy	Politics	Negative	1	Y
13	UK	5	John	01-Jan	Politics and Economy	Politics	Neutral	2	Y
21	UK	7	Alex	03-Jan	Weather and Football	Weather	Negative	1	N
55	UK	5	John	03-Jan	Politics and Economy	Economy	Negative	1	Y
123	UK	7	Alex	06-Jan	Weather and Football	Weather	Neutral	2	N
123	UK	7	Alex	06-Jan	Politics and Economy	Economy	Negative	1	Y
132	UK	1	Henry	08-Jan	Weather and Football	Weather	Positive	3	Y
154	UK	2	Milly	08-Jan	Politics and Economy	Politics	Neutral	2	Y
321	UK	6	Matt	08-Jan	Weather and Football	Football	Neutral	2	N
516	UK	7	Alex	16-Jan	Weather and Football	Weather	Neutral	2	Y
516	UK	7	Alex	16-Jan	Politics and Economy	Economy	Neutral	2	Y
516	UK	7	Alex	16-Jan	Weather and Football	Football	Negative	1	N
1846	UK	3	Annie	17-Jan	Politics and Economy	Economy	Positive	3	Y
4564	UK	7	Alex	21-Jan	Politics and Economy	Politics	Neutral	2	Y
5231	UK	4	Jim	21-Jan	Politics and Economy	Politics	Positive	3	Y
6854	UK	2	Milly	23-Jan	Politics and Economy	Politics	Positive	3	Y
21352	UK	7	Alex	29-Jan	Politics and Economy	Economy	Neutral	2	Y

[v-jianboli-msft · Accepted solution]

Hi @junglejimsvet ,

Please try:

Negative = 
VAR _a =
    SUMMARIZE (
        'Table',
        'Table'[Parent Topic],
        'Table'[Child Topic],
        'Table'[Customer ID],
        "Reaction",
            CALCULATE (
                MAX ( 'Table'[Reaction] ),
                FILTER ( 'Table', [Date] = MAX ( 'Table'[Date] ) )
            )
    )
RETURN
    COUNTX ( FILTER ( _a, [Reaction] = "Negative" ), [Customer ID] )

Neutral = 
VAR _a =
    SUMMARIZE (
        'Table',
        'Table'[Parent Topic],
        'Table'[Child Topic],
        'Table'[Customer ID],
        "Reaction",
            CALCULATE (
                MAX ( 'Table'[Reaction] ),
                FILTER ( 'Table', [Date] = MAX ( 'Table'[Date] ) )
            )
    )
RETURN
    COUNTX ( FILTER ( _a, [Reaction] = "Neutral" ), [Customer ID] )


Positive = 
VAR _a =
    SUMMARIZE (
        'Table',
        'Table'[Parent Topic],
        'Table'[Child Topic],
        'Table'[Customer ID],
        "Reaction",
            CALCULATE (
                MAX ( 'Table'[Reaction] ),
                FILTER ( 'Table', [Date] = MAX ( 'Table'[Date] ) )
            )
    )
RETURN
    COUNTX ( FILTER ( _a, [Reaction] = "Positive" ), [Customer ID] )

Average Shift = 
VAR _a =
    SUMMARIZE (
        'Table',
        'Table'[Parent Topic],
        'Table'[Child Topic],
        'Table'[Customer ID],
        'Table'[Reaction]
    )
VAR _b =
    SUMMARIZE (
        _a,
        'Table'[Parent Topic],
        'Table'[Child Topic],
        'Table'[Customer ID],
        "Shift",
            IF (
                COUNTX ( 'Table', [Reaction] ) > 1,
                CALCULATE (
                    MAX ( 'Table'[Reaction as number] ),
                    FILTER ( 'Table', [Call Name] = MAX ( 'Table'[Call Name] ) )
                )
                    - CALCULATE (
                        MIN ( 'Table'[Reaction as number] ),
                        FILTER ( 'Table', [Call Name] = MIN ( 'Table'[Call Name] ) )
                    )
            )
    )
RETURN
    AVERAGEX ( _b, [Shift] )

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[junglejimsvet]

You are amazing - have managed to put it into our model and worked perfectly - how you experts are able to do this is beyond me:-)  Thankyou so much!!!!!

[junglejimsvet]

Amazing - will give it a go and let you know how I do - can't believe how helpful people are in this community!

[junglejimsvet]

Apologies - I think I have made the post too big to get a response:(

Simplifying the question....   How to I use dax to only retrieve a field for the latest interaction with a customer?

e.g. I have seen the customer 3 times, I want to capture his satisfaction on the last visit... 

(ideally I need to segment this by product so if the customer bought 2 hoovers and an iron I would see the latest hoover satisfaction and the latest iron satisfaction)