Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper I
Helper I

Matrix Table - Filtering child records while maintaining parent rows

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 NameContact PositionAccount Name
John SmithCEOCity A
Mary RodriguezCFOCity A
Michael JamesAccountingCity A
Maria MartinezHRCity A
James JohnsonCEOCity B
John BrownCFOCity B
Eric EricsonAccountingCity B
Bob JohnsonHRCity B


My Representatives
Representative 1
Representative 2


Contact NameActivity TypeActivity RepActivity Date
Maria MartinezEventRepresentative 19/1/2022
John SmithSite VisitRepresentative 18/30/2022
John SmithPhone CallRepresentative 18/25/2022
Michael JamesE-MailRepresentative 27/15/2022
Maria MartinezSite VisitRepresentative 27/13/2022
Maria MartinezEventRepresentative 17/10/2022
Maria MartinezE-MailRepresentative 16/10/2022
John BrownPhone CallRepresentative 28/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))


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.

Super User
Super User

Hi @morgtd30 

please try

Last Activity Type =
VAR max_date =
        MAX ( contacts[wc_lastactivitydate] ),
        ALLEXCEPT ( contacts, contacts[Account Name], contacts[wc_contactrole] ),
        ALLSELECTED ( contacts[Activity Rep] )
VAR max_activitytype =
        MAX ( contacts[wc_lastactivitytype] ),
        FILTER (
            ALLEXCEPT ( contacts, contacts[Account Name] ),
            contacts[wc_lastactivitydate] = max_date
        ALLSELECTED ( contacts[Activity Rep] )
    IF (
        ISINSCOPE ( contacts[fullname] ),
        SELECTEDVALUE ( contacts[wc_lastactivitytype] ),

@tamerj1 Thank you for you time. Unfortunately that didn't work for me.

In the new ALLSELECTED lines I made them 

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.

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors