calculated column function similar to returning customer measure in DAX

Question

I am trying to develop DAX calculated columns that indicate changes in trainer assignment over time. I'm relatively new to Power BI and DAX. Below is an example of the fact table. For now I am looking at one trainer (Mario) and comparing whether people are still assigned to him, newly assigned, or have the assignment removed based on the visit date and trainer columns.

I will be creating a dashboard but in the simplest view I want the output to be like this (as well as most recent the visit date):

where "newly assigned" applies if the previous visit date had a different trainer listed and now has Mario listed or this is the first visit, "still assigned" if the two most recent visits are both with Mario, and "assignment removed" if another trainer is listed for a visit after Mario.

After searching online forums I think this is similar to the returning/lost customer measures that are readily available but I need the output at the row level so I think that I need to use calculated columns. Based on these searches I created a column that outputs 1 if Mario is the trainer and tried using MIN/MAX in another column which didn't work.

Here is the working calculated column:

Mario = 
VAR IfMario = CALCULATE (
    FIRSTNONBLANK( fact[Trainer], 1),
    FILTER( ALL (fact[ID]), fact[ID] = "Mario" )
)

RETURN
SWITCH (
TRUE(),
IfMario = "Mario", 1,
0
     )

I am not sure how to proceed from here. Thank you for the help!

RADO RADO · Accepted Answer · 2018-08-21T01:07:55

To create such view, you don't need calculated columns (unless you have other reasons not mentioned in your question). Measures will be easier to build.

Measure 1:

Last Visit Date = MAX(fact[Date])

Measure 2:

Current Trainer    =
VAR Current_Date = [Last Visit Date]
RETURN
    CALCULATE ( VALUES ( fact[Trainer] ), fact[Date] = Current_Date )

Measure 3:

Previous Trainer    =
VAR Current_Date = [Last Visit Date]
VAR Previous_Date = CALCULATE ( MAX ( fact[Date] ), fact[Date] < Current_Date )
RETURN
    CALCULATE ( VALUES ( fact[Trainer] ), fact[Date] = Previous_Date )

Measure 4:

Category    =
SWITCH (
    TRUE (),
    [Current Trainer] =  "Mario" && [Previous Trainer] =  "Mario", "Still Assigned",
    [Current Trainer] =  "Mario" && [Previous Trainer] <> "Mario", "Newly Assigned",
    [Current Trainer] <> "Mario" && [Previous Trainer] =  "Mario", "Assignement Removed"
)

Put Names on the matrix row, drop measures 4 and 1 to the values, and you will have your report.

Notes:

I think provided example contains a mistake (Jon Snow category should be 'Assignment Removed', not 'Newly assigned';
there is no need to hard-code "Mario", it can be a dynamically selected Trainer;
the code relies on the assumption of one trainer per name per date, otherwise you will get an error (which you can protect against by using If(Hasonevalue[fact[Trainer]), ...

[EDIT: protecting from multiple trainers error]

If you have cases where a customer might have multiple trainers per same date, VALUES will return more than 1 trainer, which gives an error. Possible solution:

Current Trainer Count:=
VAR Current_Date = [Most Recent]
RETURN  
    COUNTROWS( CALCULATETABLE(Customer, Customer[Date] = Current_Date ))

Such measure will return number of current trainers per customer. Then modify [Current Customer] measure:

Current Trainer:=
VAR Current_Date = [Most Recent]
RETURN
 IF([Current Trainer Count] = 1,  
     CALCULATE( VALUES(Customer[Trainer]), Customer[Date] = Current_Date ),
     "Multiple Trainers")

Instead of "Multiple Trainers" text, you can return whatever you need. For example, you can return current trainer count:

Current Trainer:=VAR Current_Date = [Most Recent]
RETURN
  IF([Current Trainer Count] = 1,  
     CALCULATE( VALUES(Customer[Trainer]), Customer[Date] = Current_Date ),
     [Current Trainer Count] & " trainers"    )

So, instead of "multiple trainers", measure will return "2 trainers", etc.

An alternative approach is to concatenate all trainers into a delimited list. For example:

Current Trainer=
VAR Current_Date = [Most Recent]
RETURN
    CONCATENATEX (
        CALCULATETABLE ( Customer, Customer[Date] = Current_Date ),
        Customer[Trainer], "," )

Such measure does not need protection, because in case of multiple trainers it will simply list them all:

Current Trainer
"Mario, Yoshi"

Of course, you can also use "Current Trainer Count" in the report, to quickly see such scenarios.

The same logic applies to the Previous Trainer.

Finally, you need to protect your [Category] measure from multiple-trainer scenarios. For example:

Status=
IF([Current Trainer Count] > 1 || [Previous Trainer Count] > 1, "Multiple Trainers", 
SWITCH
(TRUE(),
[Current Trainer] = "Mario" && [Previous Trainer] = "Mario", "Same",
[Current Trainer] = "Mario" && [Previous Trainer] <> "Mario","New",
[Current Trainer] <> "Mario" && [Previous Trainer] = "Mario", "Lost"
))

calculated column function similar to returning customer measure in DAX

1 Answers