Data Slicers not working properly with Excel 2016

[LordRA]

Dear Community

I have created a Dashboard with Excel 365 (V 16.0.11601) using data from an SQL Server, via Power Query and Pivots / Charts. The Pivots and Charts are linked with data slicers in order to filter data.

The idea is, that the user does not need to connect to the SQL Server but uses directly the data from Power Pivot. With my Excel versions the slicers work as expected.

Customers which are using Office 2016 Professional Plus (V 16.0.4738) complained, that the slicers and pivot-filters do not work.

We were able to reproduce the issue with Excel 2016: If we click on a slicer element or select a filter-value in a pivot-table, excel is shortly "calculating" but then the filter / slicer gets reset and the data is not filtered or refreshed. No error is shown.

Anyone has an idea, what's the reason of this behaviour?

[#2]

@LordRA I'm having the same problem. Were you able to resolve the issue?

[#3]

@Aswa09Yes, i solved it. The reason of this behaviour was a Measure I used in my Pivots, which returned an error in Office 2016, but not in Office 365. Turns out, there was slight change of the DAX-Formula logic between the two Office versions. Once I found a way the DAX-Formula worked in both versions, the Data Slicers worked like expected.

The DAX-Formula which caused the error in my case was:

=IF([PERFORMANCE]>=0;[PERFORMANCE];"")

Since the field "[PERFORMANCE]" was a number, and my IF-Formula could return either a number or a string, Office 2016 complained about data-type mismatch (Office 365 doesn't seem to bother).

Therefore I changed to formula to:

=IF([PERFORMANCE]>=0;[PERFORMANCE];BLANK())

This way the formula works in both Excel versions.

Cheers, LordRA