Forum Discussion

LordRA's avatar
LordRA
Copper Contributor
May 08, 2019

Data Slicers not working properly with Excel 2016

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 ...
BI & Data Analytics
Slicer
Aswa09's avatar
Aswa09
Copper Contributor
Sep 20, 2019

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

LordRA's avatar
LordRA
Copper Contributor
Sep 23, 2019

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

Resources