Forum Discussion

morgtd30's avatar
morgtd30
Helper I
3 years ago
Solved

Making Matrix Table Aggregates Match

I have a column that shows the last date a contact was interacted with. I'm having that value being aggregated to "Latest" to have it display on the account it belongs to if the group is not expanded...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi morgtd30 ,

     

    You could use ISINSCOPE() function to return specific value at subtotal.

    measure1 =
var max_date = calculate(max([date]),allexcept('table',[city]))
var max_column1 = calculate(max([column1]),FILTER(allexcept('table',[city]),[date] = max_date))

return
if(isinscope([human]),selectedvalue([column1]),max_column1)

    The same for column2.

     

    Best Regards,

    Jay

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi morgtd30 ,

 

You could use ISINSCOPE() function to return specific value at subtotal.

measure1 =
var max_date = calculate(max([date]),allexcept('table',[city]))
var max_column1 = calculate(max([column1]),FILTER(allexcept('table',[city]),[date] = max_date))

return
if(isinscope([human]),selectedvalue([column1]),max_column1)

The same for column2.

 

Best Regards,

Jay

morgtd30's avatar
morgtd30
Helper I
3 years ago

I fixed the issue. I had the wrong date field in the first var. It works perfectly now!

 

Changed to:

 

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

return
if(isinscope(contacts[fullname]),selectedvalue(contacts[wc_contactrole]),max_contactrole)