Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Solved! Go to 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)
)
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |