Forum Discussion

morgtd30's avatar
morgtd30
Helper I
3 years ago

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...
Need Help
tamerj1's avatar
tamerj1
Community Champion
3 years ago

Hi morgtd30 

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's avatar
morgtd30
Helper I
3 years ago

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.