New Column that produces a single value based on the date

chad.sharpe · January 27, 2021, 4:03pm

Hi,
Hopefully i uploaded the test PBX correctly.

The data set i have is a list of customers surveyed over a period of time. Based on the customers response they are classified as (Detractor, Passive, Promoter). I want a new column that tells me what their first Survey result was.

I’ve tried many variations of minx against the date, but i simply can’t get it to work. I think it should be easy…lol

AntrikshSharma · January 27, 2021, 4:30pm

@chad.sharpe Customer CX.pbix (19.5 KB)

First Response = 
VAR CurrentCustomer = Sheet1[Cutomer ID]
VAR CurrentDate = Sheet1[Date]
VAR RecordsOfCurrentCustomer =
    FILTER ( Sheet1, Sheet1[Cutomer ID] = CurrentCustomer )
VAR FirstResponseDate =
    MINX ( RecordsOfCurrentCustomer, Sheet1[Date] )
VAR FirstResponse = -- CONCATENATEX for securing the code in case of duplicates.
    CONCATENATEX (
        FILTER ( RecordsOfCurrentCustomer, Sheet1[Date] = FirstResponseDate ),
        Sheet1[Response],
        ", "
    )
RETURN
    FirstResponse

.

First Response 2 = 
VAR CurrentCustomer = Sheet1[Cutomer ID]
VAR CurrentDate = Sheet1[Date]
VAR RecordsOfCurrentCustomer =
    CALCULATETABLE (
        Sheet1,
        Sheet1[Cutomer ID] = CurrentCustomer,
        REMOVEFILTERS ( Sheet1 )
    )
VAR FirstResponseDate =
    MINX ( RecordsOfCurrentCustomer, Sheet1[Date] )
VAR FirstResponse =
    -- CONCATENATEX for securing the code in case of duplicates.
    CONCATENATEX (
        FILTER ( RecordsOfCurrentCustomer, Sheet1[Date] = FirstResponseDate ),
        Sheet1[Response],
        ", "
    )
RETURN
    FirstResponse

Created a measure as well.

First Response Measure = 
VAR CurrentCustomer = SELECTEDVALUE ( Sheet1[Cutomer ID] )
VAR CurrentDate = SELECTEDVALUE ( Sheet1[Date] )
VAR RecordsOfCurrentCustomer =
    FILTER ( 
        ALL ( Sheet1 ), 
        Sheet1[Cutomer ID] = CurrentCustomer
    )
VAR FirstResponseDate =
    MINX ( RecordsOfCurrentCustomer, Sheet1[Date] )
VAR FirstResponse = -- CONCATENATEX for securing the code in case of duplicates.
    CONCATENATEX (
        FILTER ( RecordsOfCurrentCustomer, Sheet1[Date] = FirstResponseDate ),
        Sheet1[Response],
        ", "
    )
RETURN
    FirstResponse

chad.sharpe · January 27, 2021, 4:36pm

This is perfect!

Everything I need. To get the last response I just change the min to max in your date formula.

AntrikshSharma · January 27, 2021, 4:37pm

@chad.sharpe Awesome! That’s why I like to split everything in variables so that it is easier for anyone to make adjustments!!

chad.sharpe · January 27, 2021, 5:02pm

Not to go off topic :)… are there any good youtube clips for using variables?

I normally don’t use them and find them a little confusing. Your’s are laid out very nice and stright forward in the PBX so I can easily follow and transpose them in my real dataset.

AntrikshSharma · January 27, 2021, 5:28pm

@chad.sharpe I think there are a couple of videos out there. I believe that it is best to have a single variable for each task, for example ideally I would split that last variable split in two parts. The whole reason of variables is to debug, optimize and improve readability.

Here is an example of nested variables and what it returns: