Calculated table with SUMMARIZE that changes dynamically based on disconnected filter/slicer

[hymieho]

I have a report with a disconnected slicer that also includes a matrix to show details:

I would like the details matrix to only show records that match the criteria of the slicers. "LEAD CREATED" is based on a disconnected table called Lead Date Created Band, and I created a new calculated table as:

Leads Detail = 
VAR CompareDate = IF(HASONEVALUE('Lead Date Created Band'[Band]),
                  SWITCH(VALUES('Lead Date Created Band'[ID]),
1,DATE(2017,7,3), -- last Monday
2,DATE(2017,7,1), -- this month
3,Date(2017,7,1), -- this quarter
4,DATE(2017,1,1)), -- this year
DATE(1900,1,1)) -- if no selection then show for all time
RETURN
  SUMMARIZECOLUMNS(
      Leads[Sales Professional],
      Leads[Lead Source],
      Leads[Lead Status],
      Leads[Customer Name],
      Leads[Lead Name],
      Leads[Product],
      Leads[Lead Created],
      Leads[Lead Last Modified],
      Leads[Lead Last Activity],
      Leads[.],
 FILTER(Leads, Leads[Lead Created]>= CompareDate))

The last FILTER statement does not have any effect--the table always shows all records of all time. Is what I am attempting possible / what am I doing wrong?

Here is the disconnected table:

 

[hymieho]

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. 

[v-huizhn-msft]

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

[hymieho · Accepted solution]

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)
)

[v-huizhn-msft]

Hi @hymieho,

Congratulations, please mark your solution as answer, so others can find the workaround clearly.

Best Regards,
Angelia