Forum Discussion
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...
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
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
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))returnif(isinscope(contacts[fullname]),selectedvalue(contacts[wc_contactrole]),max_contactrole)Jay thank you for the help.
I'm not getting any values at summarized at the top for that new measure:It should bring 948000004 to the top.
Here's an example of how my tables are built:
Account Name City 1 City 2 Contact Name Account Name Contact Role Contact Type Contact Date Human 1 City 1 948000000 E-Mail 8/8/2022 Human 2 City 1 948000002 E-Mail 8/7/2022 Human 3 City 1 948000004 Site Visit 8/18/2022 Human 4 City 1 948000010 E-Mail Here's my measure:
ActLastActivityType =var max_date = calculate(max('Date'[Date]),allexcept(contacts,contacts[Account Name]))var max_contactrole = calculate(max(contacts[wc_contactrole]),filter(allexcept(contacts,contacts[Account Name]),contacts[wc_lastactivitydate] = max_date))returnif(isinscope(contacts[fullname]),selectedvalue(contacts[wc_contactrole]),max_contactrole)
