Transform Row Value based on multiple other criteria

[ScottA]

Hi All

I’ve got a data set that looks like the below. Example data, but I’ve tried to illustrate that one data set contains data for multiple employees against many different categories.

I’m specifically interested in the ‘Overnight’ Category at the moment.

Rows 1 and 3 of the data refer to employee 123 overnighting in Hamilton. They actually only stay in Hamilton on the night of the 22/05, but the logic is such that the overnight ‘starts’ on the 22/05 and ‘finishes’ on 23/05, hence the 2 rows of data.

The last 3 rows of the table illustrate a similar scenario, in that the employee stays in Queenstown on the night of the 1st and 2nd of June, and leaves Queenstown on the 3rd June.

I need to be able to transform the data such that if there are records for the same employee, of category ‘Overnight’, which fall on consecutive dates then it only counts the first instance. E.g. the Hamilton overnight counts as 1 and the Queenstown overnight counts as 2, versus the 2 and 3 respectively which come through in the original report. As in the table, it’s worth noting that data tends to be in date order based on all employees so its possible that the consecutive overnight dates are not necessarily in consecutive rows.

See ideal output column – doesn’t have to be a new column, just trying to illustrate the ideal result such that it can be used in graphs etc

The extent of my knowledge so far has been self taught through online resources such as this, so apologies if this is a stupid question. I'm using power BI desktop and the end result is to be used in a dashboard. So far I've managed to get by with creating new measures and columns using DAX to achieve what I need.

Thanks for your help!

Employee Number	Resource	Location	Role	Position 1	Position 2	Date	Category	Detail	Value		Ideal output (overnight category only)
123	Office Worker	AKL	Accounts	Manager		22/05/2018	Overnight	Hamilton	1		1
205	Engineer	WLG	Technician	Assistant	2IC	22/05/2018	Worked Mins	Worked Mins	360
123	Office Worker	AKL	Accounts	Manager		23/05/2018	Overnight	Hamilton	1		0
205	Engineer	WLG	Technician	Assistant	2IC	23/05/2018	Worked Mins	Worked Mins	600
123	Office Worker	AKL	Accounts	Manager		24/05/2018	Leave	Sick	1
205	Engineer	WLG	Technician	Assistant	2IC	24/05/2018	Worked Mins	Worked Mins	510
123	Office Worker	AKL	Accounts	Manager		25/05/2018	Leave	Sick	1
205	Engineer	WLG	Technician	Assistant	2IC	25/05/2018	Worked Mins	Worked Mins	500
123	Office Worker	AKL	Accounts	Manager		1/06/2018	Overnight	Queenstown	1		1
123	Office Worker	AKL	Accounts	Manager		2/06/2018	Overnight	Queenstown	1		1
123	Office Worker	AKL	Accounts	Manager		3/06/2018	Overnight	Queenstown	1		0

[v-yuta-msft · Accepted solution]

Hi ScottA,

To achieve your requirement,  create a calculate column using DAX formula like this:

output =
CALCULATE (
    COUNT ( Table1[Value] ),
    FILTER (
        Table1,
        Table1[Date]
            = EARLIER ( Table1[Date] ) + 1
            && Table1[Employee Number] = EARLIER ( Table1[Employee Number] )
            && Table1[Category] = "Overnight"
    )
)

The result is as below and you can refer to PBIX here: https://www.dropbox.com/s/ecb7a6zdncdcxl4/Transform%20Row%20Value%20based%20on%20multiple%20other%20...

 

Regards,

Jimmy Tao

[v-yuta-msft · Accepted solution]

Hi ScottA,

To achieve your requirement,  create a calculate column using DAX formula like this:

output =
CALCULATE (
    COUNT ( Table1[Value] ),
    FILTER (
        Table1,
        Table1[Date]
            = EARLIER ( Table1[Date] ) + 1
            && Table1[Employee Number] = EARLIER ( Table1[Employee Number] )
            && Table1[Category] = "Overnight"
    )
)

The result is as below and you can refer to PBIX here: https://www.dropbox.com/s/ecb7a6zdncdcxl4/Transform%20Row%20Value%20based%20on%20multiple%20other%20...

 

Regards,

Jimmy Tao

[ScottA · Accepted solution]

Hi Jimmy

Thank you very much, this formed 90% of the basis of my solution. 

For other's benefit, I had a couple of unexpected consequences when I used the suggested solution;

1) The calculate column produced 0's, 1's, and 2's - I only expected 0's and 1's

2) The calculate column produced non zero values against rows that were not the overnight category.

As far as I can tell both of these were generated by situations I hadn't considered when i posted the example data in original post;

1) Multiple night overnights could be in different cities. E.g. In the queenstown example above, if night 2 had been in Wanaka the data would be across 4 rows instead of 3 and it double counts the first day - see below.

Employee Number	Resource	Location	Role	Position 1	Position 2	Date	Category	Detail	Value	Ideal output (overnight category only)	output
123	Office Worker	AKL	Accounts	Manager		01/06	Overnight	Queenstown	1	1	2
123	Office Worker	AKL	Accounts	Manager		02/06	Overnight	Queenstown	1	0	1
123	Office Worker	AKL	Accounts	Manager		02/06	Overnight	Wanaka	1	1	1
123	Office Worker	AKL	Accounts	Manager		03/06	Overnight	Wanaka	1	0

2) It was common (but not reflected in the example data) that an employee would have something else (e.g. Leave, or worked mins) the same day they started the overnight, so it would count 1 against the other category on this first day of the overnight.

I managed to rectify both by making a small adjustment to Jimmy's formula, as below.

CALCULATE(COUNT(Table1[Value]), FILTER(Table1, Table1[Date] = EARLIER(Table1[Date]) +1 && Table1[Employee Number] = EARLIER(Table1[Employee Number]) && EARLIER(Table1[Category]) = "Overnight"),FILTER(Table1,Table1[Detail]=EARLIER(Table1[Detail])))

Anyway good to go now, thanks again!