cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hymieho
Resolver I
Resolver I

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

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

screenshot1.jpg

 

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:

 disconnected.JPG

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

 

 

model2.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

leads 2.png

View solution in original post

4 REPLIES 4
hymieho
Resolver I
Resolver I

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

 

 

model2.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

leads 2.png

View solution in original post

Hi @hymieho,

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

Best Regards,
Angelia

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.