Filter by two columns

[Anonymous]

Hi,
I have a table with reservations like this:

ReservationID	Area	Date	Person
1	1st floor	01.11.2020	A
2	kitchen	02.11.2020	A
3	kitchen	03.11.2020	C
4	garden	01.11.2020	D
5	garden	02.11.2020	E
6	kitchen	02.11.2020	F
7	1st floor	01.11.2020	G

I'd like to have a measure that gives me all contacts of a selected person. Contacts are based on sameDate AND sameArea.
The result should look like this ("All contacts of Person A"):

Person	PrimaryContacts
A	F, G

F and G because they we're at the same area at the same date.

Do you have any ideas to solve this? Please help me!
Thank you

[camargos88 · Accepted solution]

@Anonymous ,

Try this measure:

_Contact = 
VAR _person = SELECTEDVALUE('Table'[Person])
VAR _tblAreaDate = SELECTCOLUMNS(ADDCOLUMNS(SUMMARIZE(FILTER(ALL('Table'), 'Table'[Person] = _person), 'Table'[Area], 'Table'[Date]), "Combine", COMBINEVALUES(",", 'Table'[Area], 'Table'[Date])), "Combine", [Combine])
VAR _tblMatch = CALCULATETABLE(
                VALUES('Table'[Person]), FILTER(ALL('Table'), COMBINEVALUES(",", 'Table'[Area], 'Table'[Date]) in _tblAreaDate && 'Table'[Person] <> _person))


RETURN CONCATENATEX(_tblMatch, 'Table'[Person], ",")

[AlB · Accepted solution]

Hi @Anonymous 

1. Place [Person] in a table visual

2. Create this measure and place it in the visual

Primary contacts = 
VAR datesAreas_ = SUMMARIZE ( Table1, Table1[Area], Table1[Date] )
VAR currentPerson_ = SELECTEDVALUE ( Table1[Person] )
VAR contacts_ =
    CALCULATETABLE (
        DISTINCT ( Table1[Person] ),
        datesAreas_,
        Table1[Person] <> currentPerson_
    )
RETURN
    CONCATENATEX ( contacts_, [Person], ", " )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[camargos88 · Accepted solution]

@Anonymous ,

Try this measure:

_Contact = 
VAR _person = SELECTEDVALUE('Table'[Person])
VAR _tblAreaDate = SELECTCOLUMNS(ADDCOLUMNS(SUMMARIZE(FILTER(ALL('Table'), 'Table'[Person] = _person), 'Table'[Area], 'Table'[Date]), "Combine", COMBINEVALUES(",", 'Table'[Area], 'Table'[Date])), "Combine", [Combine])
VAR _tblMatch = CALCULATETABLE(
                VALUES('Table'[Person]), FILTER(ALL('Table'), COMBINEVALUES(",", 'Table'[Area], 'Table'[Date]) in _tblAreaDate && 'Table'[Person] <> _person))


RETURN CONCATENATEX(_tblMatch, 'Table'[Person], ",")

[mahoneypat]

You can use this measure expression in a table visual with your Person column to get the shown result.  Replace Reservations with your actual table name.  Note this has two CONCATENATEX functions.  A virtual table is made for all the combinations of place and date for that person.  The first concat combines all the people that overlapped in each place time with commas.  The second concat combines those individual list into a full list separate by semi colons.

SamePlaceDate =
VAR thisperson =
    MIN ( Reservations[Person] )
VAR summary =
    ADDCOLUMNS (
        SUMMARIZE (
            Reservations,
            Reservations[Area],
            Reservations[Date]
        ),
        "overlap",
            CONCATENATEX (
                CALCULATETABLE (
                    DISTINCT ( Reservations[Person] ),
                    Reservations[Person] <> thisperson
                ),
                Reservations[Person],
                ", "
            )
    )
RETURN
    CONCATENATEX (
        summary,
        [overlap],
        "; "
    )

Regards,

Pat