FILTERS-DAX AND DEFAULTING

Question

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?

sergeibaklan · Answer

Hi David,&nbsp;Here is quite old article with workaround&nbsp;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.

wyn hopkins · Answer

David,&nbsp;Could you put a slicer in conjunction with &nbsp;IF HASONEFILTER&nbsp;&nbsp;&nbsp;Measure1=IF (
    HASONEFILTER ( [Boolean] ),
    SUM ( [Cost] ),
    CALCULATE ( SUM ( [Cost] ), Data[Boolean] = True )
)&nbsp;&nbsp;

sergeibaklan · Answer

Wyn,&nbsp;I tried once that approach but it disturbs&nbsp;end users - they see the filter is not selected, however have the&nbsp;result for some "default" filter, not obvious which one.&nbsp;&nbsp;Another point is to see results for not filtered data, additional&nbsp;set of measures is to be used.&nbsp;However, in some scenarios&nbsp;that could work.

david pitts · Answer

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.

wyn hopkins · Answer

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."

Forum Discussion

FILTERS-DAX AND DEFAULTING

5 Replies