Forum Discussion
How to link a slicer simultaneously to both a PivotTable and a normal table
This doesn't directly answer your question but, since I have always disliked Table filters that simply hide rows, I took a slightly different route. Namely, I linked the slicers to a Pivot Table and then read the PT Row Labels in order to apply the 365 FILTER function to the source data table.
= LET(
include, BYROW(InputTable[Name]=TOROW(rowLabels, 3), ORλ),
data, FILTER(InputTable, include),
totals, HSTACK("Totals", "", SUM(TAKE(data,,-1))),
VSTACK(InputTable[#Headers], data, totals)
)Hello PeterBartholomew1
Thank you for this. I've been trying to do something like that and your script does exactly what I was looking for. Now, I have an issue with how some data or empty values in the source table are getting displayed in the "365 FILTER" table. For example, in my source table I have a date-formatted column (yyyy-mm-dd), but empty cells show up as 1900-01-01. How can I fix that?
I tried added a variable, example (the column in question is "date_uat"=LET( px?, BYROW(InputTable[PX]=UNIQUE(TOROW(rowPX,3)), ORλ), status_group?, BYROW(InputTable[Status_Group]=UNIQUE(TOROW(rowStatus,3)), ORλ), date_uat_corrected, IF(InputTable[Date_UAT]="1900-01-01", "", InputTable[Date_UAT]), data, FILTER(InputTable, px?*status_group?), corrected_data, HSTACK(InputTable[PX], InputTable[Status_Group], date_uat_corrected), VSTACK(InputTable[#Headers], corrected_data) )In this instance it may well be easier to change the number formatting than the formula.
A number format
yyyy-mm-dd;;;@will show blank cells where zero values are encountered. If you need the formula corrected, then the problem is that any Excel date is a highly formatted number, whereas "1900-01-01" is a text string. Since it is not converted by the formula, the condition evaluates to FALSE and returns the zero date. Correct versions include
date_uat_corrected, IF(InputTable[Date_UAT]=0, "", InputTable[Date_UAT]), or date_uat_corrected, IF(InputTable[Date_UAT], InputTable[Date_UAT], ""),where the second version uses the fact that dates, as positive numbers, will be coerced to TRUE when used where the formula expects a Boolean.
Nice one too. Doable with > 1 slicer?
Doable, yes. But I don't know whether it is the best approach or whether it would be better to have a separate helper pivot table for each dimension.
= LET( region?, BYROW(InputTable[Region] = UNIQUE(TOROW(rowLabels1, 3)), ORλ), sector?, BYROW(InputTable[Sector] = UNIQUE(TOROW(rowLabels2, 3)), ORλ), data, FILTER(InputTable, region? * sector?), totals, HSTACK({"Totals", "", ""}, SUM(TAKE(data, , -1))), VSTACK(InputTable[#Headers], data, totals) )Do you mind if I update the article I shared the other day, adding a link to this discussion and mentionning your 365 alternative?
(interesting to note that since I posted the article it got 20 more views, still nobody found it helpful)
