How to find the people met each other in a same place

[SadStatue]

Hi everyone,

my fact table showing the shifts details as below:

Shift ID	Staff Name	Location Name	shift start	shift end
1001	A	aa	10/08/2020 13:00	10/08/2020 18:00
1002	B	aa	10/08/2020 13:00	10/08/2020 15:00
1003	C	aa	10/08/2020 13:00	10/08/2020 15:00
1004	D	aa	10/08/2020 13:00	10/08/2020 17:00
1005	E	aa	10/08/2020 19:00	10/08/2020 20:00
1006	F	aa	12/08/2020 21:30	13/08/2020 6:30
1007	G	aa	12/08/2020 21:30	13/08/2020 6:30
1008	H	bb	13/08/2020 14:00	13/08/2020 15:00
1009	I	bb	13/08/2020 15:00	13/08/2020 18:00
1010	J	bb	13/08/2020 13:00	13/08/2020 14:00
1011	A	bb	13/08/2020 13:00	13/08/2020 16:00
1012	K	bb	13/08/2020 17:00	13/08/2020 21:00

I want to make a report that when we select a staff it show me all other staff that was in contact with the selected staff.

so if I select A it should be able to identifie all othe staff who was in contact with A:

Selected staff	locatiton	date	met staff
A	aa	10/08/2020	B
A	aa	10/08/2020	C
A	aa	10/08/2020	D
A	bb	13/08/2020	H
A	bb	13/08/2020	I
A	bb	13/08/2020	J

Could you please help me define a measure that can provide such a result. or if I need to make any change to data model by creating more table and relations?

Thanks,

[pranit828]

HI @SadStatue 

try this create a table as below and filter on [Staff Name]

Table = 
GENERATE(
    Table,
    SELECTCOLUMNS(
        CALCULATETABLE(
            VALUES( Table[Staff Name] ),
            (EARLIER((Table[shift start]) <= Table[shift start] &&
             EARLIER(Table[shift end]) > Table[shift Start]) ||
            (EARLIER((Table[shift end]) >= Table[shift start] &&
             EARLIER(Table[shift start]) < Table[shift Start])
            )
        ),
        Table[Shift ID], Table[Staff Name], Table[Location Name], Table[Staff Name],"MetStaff"
    )
)

[SadStatue]

Hi Pranti,

Thank you for your response But I cannot create such a table as it seems there are some syntax erros cus it doesn't recognise the column in second EARLIER.

It would be appreciated if you re-look at your response to see why its not applicable

Cheers,

[Greg_Deckler]

@SadStatue - Tough one. It's 1AM here and ..., closest I have been able to come is:

Measure 16 = 
    VAR __Name = MAX([Staff Name])
    VAR __Start = MAX([shift start])
    VAR __End = MAX ([shift end])
    VAR __Table = FILTER(ALL('Table (16)'),[Staff Name] <> __Name && ([shift start] >= __Start && [shift end] <= __End))
RETURN
    CONCATENATEX(__Table,[Staff Name],",")

Needs work. PBIX is attached below sig. It's close. Table 16, Page 16. 

[SadStatue]

Hi Greg,

Thank you for your support and quick response even on midnight!

I've tried what you suggested but as it doesnt cover the location I changed it in the following way

Measure 16 =
VAR __Name = MAX([Staff Name])
VAR __Start = MAX([shift start])
VAR __End = MAX ([shift end])
VAR __Location = MAX([location])
VAR __Table = FILTER(ALL('Table (16)'),[Staff Name] <> __Name && ([shift start] >= __Start && [shift end] <= __End) && [location]=__Location)
RETURN
CONCATENATEX(__Table,[Staff Name],",")

It provide some results but it wasn't accurate. I feel the shift time comparison is not properly defined as any cross between shifts is acceptable (If I understand the logic of the measure properly it should be sth like: ([shift start] >= __Start && [shift start] <= __End)OR([shift end] <= __End && [shift end] >= __Start) OR ([shift start] <= __Start && [shift end] >= __End) )

But still it is not working yet.

It would be great if you can have a second look at your answer and refine it in a way it works.

Cheers,

[amitchandak]

@SadStatue , try a measure like this and check

measure =
var _staff = filter(distinct(Table[shift start]), table[Staff Name] in allselected(table[Staff Name])) // or use -- in values(table[Staff Name])
return
calculate(countrows(Table),all(table[Staff Name]), filter(Table, Table[shift start] in _staff))

[SadStatue]

Hi Amit

Thank you for your response. But I couldn't create such a measure.

When I use "distinct(Table[shift start])" then I cannot use "table[Staff Name]" as the next argument. And when I just use "distinct(Table)" then I see an error of "The number of arguments is invalid. Function CONTAINSROW must have a value for each column in the table expression" although I havn't use "CONTAINSROW" at all!!

Could you please re-look at it and see whats wrong with it?

Cheers,

[v-shex-msft]

Hi @SadStatue ,

You can refer to following link to use 'crossjoin' to expand your date range and link to the raw table, then you can create a table with expand table fields and use filter to interaction with these records to achieve your requirement:

Spread revenue across period based on start and end date, slice and dase this using different dates 

Regards,

Xiaoxin Sheng