Forum Discussion

Thamer_Tarabzouni's avatar
Thamer_Tarabzouni
Copper Contributor
Oct 06, 2022

How to link a slicer simultaneously to both a PivotTable and a normal table

Hi,  I created a table from Power Query and I created several Slicers (Dept, ID, Date, Time). This to dynamically filter the list of rows displayed by the table. Also I created a pivot table to disp...
excel
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Dec 06, 2023

Thamer_Tarabzouni 

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)
  )
HansA220's avatar
HansA220
Copper Contributor
Dec 16, 2024

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

 

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Dec 16, 2024

    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.

Resources