Forum Discussion
Calculated table with SUMMARIZE that changes dynamically based on disconnected filter/slicer
Thanks Aneglia. The "Lead Created" slicer with "Since last Monday" is just the slicer's title--it is not the name of the table. I was able to achieve the desired result by using the same pattern I used to build the tornado chart that I referenced above by creating a new Leads Detail table. Leads Detail is a union of each of the time sliced data (last week, last month, etc. The same record can be in multiple tranches, so the actual data grid is populated by the source 'Leads' table. Note I have bidirectional cross-filtering turned on in the relationship between Leads and Leads Detail.
Hope this helps someone else!
~Hymie
Leads Detail =
UNION(
SUMMARIZECOLUMNS(
Leads[Lead ID],
Leads[Lead Dim ID],
Leads[Sales Professional],
Leads[Lead Source],
Leads[Lead Status],
Leads[Lead State],
Leads[Customer Name],
Leads[Lead Name],
Leads[Product],
Leads[Lead Created],
Leads[Lead Last Modified],
Leads[Lead Last Activity],
Leads[Territory],
Leads[.],
Leads[Region],
FILTER('Leads','Leads'[IsSinceLastMonday]=1),
"Band ID",1
)
,
SUMMARIZECOLUMNS(
Leads[Lead ID],
Leads[Lead Dim ID],
Leads[Sales Professional],
Leads[Lead Source],
Leads[Lead Status],
Leads[Lead State],
Leads[Customer Name],
Leads[Lead Name],
Leads[Product],
Leads[Lead Created],
Leads[Lead Last Modified],
Leads[Lead Last Activity],
Leads[Territory],
Leads[.],
Leads[Region],
FILTER('Leads', Leads[IsThisMonth]=1),
"Band ID",2
)
,
SUMMARIZECOLUMNS(
Leads[Lead ID],
Leads[Lead Dim ID],
Leads[Sales Professional],
Leads[Lead Source],
Leads[Lead Status],
Leads[Lead State],
Leads[Customer Name],
Leads[Lead Name],
Leads[Product],
Leads[Lead Created],
Leads[Lead Last Modified],
Leads[Lead Last Activity],
Leads[Territory],
Leads[.],
Leads[Region],
FILTER('Leads', Leads[IsThisQuarter]=1),
"Band ID",3)
,
SUMMARIZECOLUMNS(
Leads[Lead ID],
Leads[Lead Dim ID],
Leads[Sales Professional],
Leads[Lead Source],
Leads[Lead Status],
Leads[Lead State],
Leads[Customer Name],
Leads[Lead Name],
Leads[Product],
Leads[Lead Created],
Leads[Lead Last Modified],
Leads[Lead Last Activity],
Leads[Territory],
Leads[.],
Leads[Region],
FILTER('Leads', Leads[IsThisYear]=1),
"Band ID",4)
,
SUMMARIZECOLUMNS(
Leads[Lead ID],
Leads[Lead Dim ID],
Leads[Sales Professional],
Leads[Lead Source],
Leads[Lead Status],
Leads[Lead State],
Leads[Customer Name],
Leads[Lead Name],
Leads[Product],
Leads[Lead Created],
Leads[Lead Last Modified],
Leads[Lead Last Activity],
Leads[Territory],
Leads[.],
Leads[Region],
FILTER('Leads', 'Leads'[IsSinceLastMonday]<>1 && Leads[IsThisMonth]<>1 && Leads[IsThisQuarter]<>1 && Leads[IsThisYear]<>1),
"Band ID",-1)
)
So clearly there is no filter context when SUMMARIZECOLUMNS is called which is why it isn't working. I think this is another version of the same problem I was having in this thread so I'm going to just UNION all of the results for each LEAD CREATED tranche similar to how I did it for the tornado chart.
Hi hymieho,
Have you resolved your issue? From your screenshot, the Leads[LEAD CREATED] is "This month","This quarter","This year" etc values, which are text type. In your fiter "FILTER(Leads, Leads[Lead Created]>= CompareDate))", you compare text type and date type?
Best Regards,
Aneglia
Thanks Aneglia. The "Lead Created" slicer with "Since last Monday" is just the slicer's title--it is not the name of the table. I was able to achieve the desired result by using the same pattern I used to build the tornado chart that I referenced above by creating a new Leads Detail table. Leads Detail is a union of each of the time sliced data (last week, last month, etc. The same record can be in multiple tranches, so the actual data grid is populated by the source 'Leads' table. Note I have bidirectional cross-filtering turned on in the relationship between Leads and Leads Detail.
Hope this helps someone else!
~Hymie
Leads Detail =
UNION(
SUMMARIZECOLUMNS(
Leads[Lead ID],
Leads[Lead Dim ID],
Leads[Sales Professional],
Leads[Lead Source],
Leads[Lead Status],
Leads[Lead State],
Leads[Customer Name],
Leads[Lead Name],
Leads[Product],
Leads[Lead Created],
Leads[Lead Last Modified],
Leads[Lead Last Activity],
Leads[Territory],
Leads[.],
Leads[Region],
FILTER('Leads','Leads'[IsSinceLastMonday]=1),
"Band ID",1
)
,
SUMMARIZECOLUMNS(
Leads[Lead ID],
Leads[Lead Dim ID],
Leads[Sales Professional],
Leads[Lead Source],
Leads[Lead Status],
Leads[Lead State],
Leads[Customer Name],
Leads[Lead Name],
Leads[Product],
Leads[Lead Created],
Leads[Lead Last Modified],
Leads[Lead Last Activity],
Leads[Territory],
Leads[.],
Leads[Region],
FILTER('Leads', Leads[IsThisMonth]=1),
"Band ID",2
)
,
SUMMARIZECOLUMNS(
Leads[Lead ID],
Leads[Lead Dim ID],
Leads[Sales Professional],
Leads[Lead Source],
Leads[Lead Status],
Leads[Lead State],
Leads[Customer Name],
Leads[Lead Name],
Leads[Product],
Leads[Lead Created],
Leads[Lead Last Modified],
Leads[Lead Last Activity],
Leads[Territory],
Leads[.],
Leads[Region],
FILTER('Leads', Leads[IsThisQuarter]=1),
"Band ID",3)
,
SUMMARIZECOLUMNS(
Leads[Lead ID],
Leads[Lead Dim ID],
Leads[Sales Professional],
Leads[Lead Source],
Leads[Lead Status],
Leads[Lead State],
Leads[Customer Name],
Leads[Lead Name],
Leads[Product],
Leads[Lead Created],
Leads[Lead Last Modified],
Leads[Lead Last Activity],
Leads[Territory],
Leads[.],
Leads[Region],
FILTER('Leads', Leads[IsThisYear]=1),
"Band ID",4)
,
SUMMARIZECOLUMNS(
Leads[Lead ID],
Leads[Lead Dim ID],
Leads[Sales Professional],
Leads[Lead Source],
Leads[Lead Status],
Leads[Lead State],
Leads[Customer Name],
Leads[Lead Name],
Leads[Product],
Leads[Lead Created],
Leads[Lead Last Modified],
Leads[Lead Last Activity],
Leads[Territory],
Leads[.],
Leads[Region],
FILTER('Leads', 'Leads'[IsSinceLastMonday]<>1 && Leads[IsThisMonth]<>1 && Leads[IsThisQuarter]<>1 && Leads[IsThisYear]<>1),
"Band ID",-1)
)Hi hymieho,
Congratulations, please mark your solution as answer, so others can find the workaround clearly.
Best Regards,
Angelia