Excel slicer always greys out Dims for measure if first blank

[lukiz84]

Good morning,

I have a strange behaviour in my Excel Pivot, for testing purposes I use a very simple measure:

ETO:=IF(ISFILTERED(Calendar), 1, BLANK())

When I drag this field to a Pivot Table with nothing else present, my dim dimensions are all greyed out in my slicer (because date is not filtered, hence BLANK()).

When I add a timeline slicer for my calendar table, and filter to one date, the measure correctly displays "1", but the dimension fields are still greyed out (indicating no data) but when I select them, they work correctly (but the dimensions stay greyed out). 

What do I do wrong here?!

Thanks and BR

[lukiz84 · Accepted solution]

Hi, 

just wanted to let you know that Alberto Ferrari answered my question on sqlbi.com. Long story short, it's a bug in Excel.

"It seems to be a problem specific to the timeline filter. If you inspect the MDX queries, you will notice that the query that populates the CompanyID slicer does not feel the filter on the date, if that is coming from a timeline. If, instead of a timeline, you use a regular slicer by date, then it will work just fine.
Not feeling the filter on the date, your measure always returns blank, resulting in the graying out of the items"

Case closed.

[amitchandak]

@lukiz84 , I think you need to use column name 

ETO:=IF(ISFILTERED(Calendar[Date]), 1, BLANK())

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[lukiz84]

Hi, thanks for your swift reply. It doesn't change when I use column name.

(I'm working with SSAS Tabular, comp level 1500)

I've uploaded a very simple example but it perfectly shows my problem. As you can see the measure calculates correctly, but the slicer still greys out everything (though selectable)

https://file.io/uFRqoVsDiWM0 

thx and BR