Forum Discussion
Filter by two columns
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
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], ",")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
3 Replies
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
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
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], ",")
