Matrix Table - Filtering child records while maintaining parent rows

Question

I'm attempting to create a matrix table which displays the activity records of our representatives with our account contacts. We would like this to display most recent activities of all reps but also with the abillity to filter by rep and maintain the visibility of the parent rows.
Examples of my tables:

Account Name

City A

City B

Contact Name	Contact Position	Account Name
John Smith	CEO	City A
Mary Rodriguez	CFO	City A
Michael James	Accounting	City A
Maria Martinez	HR	City A
James Johnson	CEO	City B
John Brown	CFO	City B
Eric Ericson	Accounting	City B
Bob Johnson	HR	City B

My Representatives

Representative 1

Representative 2

Activities
Contact Name	Activity Type	Activity Rep	Activity Date
Maria Martinez	Event	Representative 1	9/1/2022
John Smith	Site Visit	Representative 1	8/30/2022
John Smith	Phone Call	Representative 1	8/25/2022
Michael James	E-Mail	Representative 2	7/15/2022
Maria Martinez	Site Visit	Representative 2	7/13/2022
Maria Martinez	Event	Representative 1	7/10/2022
Maria Martinez	E-Mail	Representative 1	6/10/2022
John Brown	Phone Call	Representative 2	8/30/2022

Currently, my table layout is in this matrix format. There is an account name, contact name hierarchy on the left which is collapsible.

The right columns are measures to show the most recent values corresponding with the Last Activity Date column:

Last Activity Type =

var max_date = calculate(max(contacts[wc_lastactivitydate]),allexcept(contacts,contacts[Account Name],contacts[wc_contactrole]))

var max_activitytype = calculate(max(contacts[wc_lastactivitytype]),filter(allexcept(contacts,contacts[Account Name]),contacts[wc_lastactivitydate] = max_date))

return

if(isinscope(contacts[fullname]),selectedvalue(contacts[wc_lastactivitytype]),max_activitytype)

This works perfectly for showing the activity of all representatives, but I have no ability to filter to a specific one.
I'd like to be able to filter to Representative 2, for example, and show the following on the table:

I've tried "going a level down" with everything to have it run off the Activities table and I can then filter successfully, but I lose the contact rows that don't have activities and we lose the ability to see which contacts are not being spoken with.

Any help is appreciated.

tamerj1 · Answer

Hi&nbsp;morgtd30&nbsp;
please try
Last Activity Type =
VAR max_date =
    CALCULATE (
        MAX ( contacts[wc_lastactivitydate] ),
        ALLEXCEPT ( contacts, contacts[Account Name], contacts[wc_contactrole] ),
        ALLSELECTED ( contacts[Activity Rep] )
    )
VAR max_activitytype =
    CALCULATE (
        MAX ( contacts[wc_lastactivitytype] ),
        FILTER (
            ALLEXCEPT ( contacts, contacts[Account Name] ),
            contacts[wc_lastactivitydate] = max_date
        ),
        ALLSELECTED ( contacts[Activity Rep] )
    )
RETURN
    IF (
        ISINSCOPE ( contacts[fullname] ),
        SELECTEDVALUE ( contacts[wc_lastactivitytype] ),
        max_activitytype
    )

morgtd30 · Answer

tamerj1&nbsp;Thank you for you time. Unfortunately that didn't work for me.In the new ALLSELECTED lines I made them&nbsp;ALLSELECTED ( contacts[wc_lastactivityrep] ). The wc_lastactivityrep producted by the same kind of measure though. Doing this made me lose the blank rows.I think I need to be able to filter by the activities[Activity Rep] field. Tried using that in the ALLSELECTED as well but then filtering changed nothing.

Forum Discussion

Matrix Table - Filtering child records while maintaining parent rows

2 Replies

Related content

Filtering Record History by Period

Data Pipeline Maintain & Job Management

How to count blank record and un-blank record using DAX in filter Calculations

Maintain historical Data

Manually maintained datasets