Forum Discussion
How to link a slicer simultaneously to both a PivotTable and a normal table
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.