FILTERS-DAX AND DEFAULTING

[David Pitts]

Is there any way in DAX to identify were a Dimension Filter has been place in the Pivot Frame?

The Issue is I am Trying to "Default" a Filter Selection Via Dax . ie You have a Filter True/False.

I want my Pivot Table to show True Data UNLESS the User Explicity Selects False.

Using the Has One Value Etc works OK, in Principle if you Drag the Filter into the Row or Column,

but if you drag into the filter Area the Filter Says ALL but the Total Only Shows the "Defaulted" Value, which is confusing / Misleading to the Audience.

Does anyone have A Hack For this?

[#2]

Hi David,

Here is quite old article with workaround http://spr.com/setting-default-member-using-tabular-mode-ssas-excel-2013/. On the othre hand I don't think something changed recently with assigning default value in tabular model.

[#3]

David,

Could you put a slicer in conjunction with  IF HASONEFILTER 

Measure1

=IF (
    HASONEFILTER ( [Boolean] ),
    SUM ( [Cost] ),
    CALCULATE ( SUM ( [Cost] ), Data[Boolean] = True )
)

[#4]

Wyn,

I tried once that approach but it disturbs end users - they see the filter is not selected, however have the result for some "default" filter, not obvious which one. 

Another point is to see results for not filtered data, additional set of measures is to be used.

However, in some scenarios that could work.

[#5]

The Problem with This Approach, is that you cannot tell if the user has selected both Values (because teh Filter is in the Pivot frame showing "ALL")  Or Not Selected either (because the Filter is not even in the Pivot Frame.)

It is Componded by Slicers Because there is No Difference in the Filter Between a Slicer Withall Members Selected and  slicer With No Members Selected.

The Closest I Got was to Create the the 3 Member Slicer "True", "False", and "Both True and False"

But this still does not get aroundthe problem that we have a confusing Stituation that if the Dim is in the Filter Frame with No Selection it will Indicate "All" and the Total will be Will be a Subset.

[#6]

Hi David,

Do you need the filter box to show ?

The slicer approach should address your aim
"I want my Pivot Table to show True Data UNLESS the User Explicity Selects False."