how to detect if field is filtered

[Anonymous]

I have a very simple data model consisting of 3 tables:

Fact Sale

columns: Date, Product, Amount

Dimension Date

columns: Date, Day Name, ...

Dimension Product

columns: Product, Category, Type, ...

I have a very simple measure:

Total Sales = SUM('Fact Sale'[Amount])

I have a page displaying this measure.  This page also has several dimension fields on the filter pane.  These fields include 'Dimension Product'[Category] and 'Dimension Product'[Type].  The cardinality of [Category] and [Type] is fairly large (~100 each), and is dynamic (i.e., I won't know their values in advance).

I have a strange requirement.  The user wants the value of Total Sales to return blank if the values selected for [Category] and [Type] is anything less than all values in the current filter context.  So, for demonstration purposes, let's say that, in the current filter context, [Category]'s "all values" are 'A', 'B', and 'C'.  In this context, Total Sales would show some value.  However, if the user filters [Category] down to just 'A' and 'B', then the user wants Total Sales to be blank.

How can I achieve this?

[AlB · Accepted solution]

Hi @Anonymous 

You can use a combination of ISFILTERED( )

ISCROSSFILTERED()

and check how many items have been selected in the slicer/filter context  with COUNT(Table1[SlicerCol]) and comparing to the total number of items CALCULATE(COUNT(Table1[SlicerCol]), ALL(Table1[SlicerCol]))

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

@AlB 

I don't understand why I need to use ISFILTERED and ISCROSSFILTERED.  Below is what I've done thus far.  Does this achieve the same effect?

Test Measure =

VAR __product_category_count_filter_context =
COUNT('Dimension Product'[Category])

VAR __product_category_count_all =
CALCULATE(
    COUNT('Dimension Product'[Category])
    ,ALL('Dimension Product'[Category])
)

VAR __product_type_count_filter_context =
COUNT('Dimension Product'[Type])

VAR __product_type_count_all =
CALCULATE(
    COUNT('Dimension Product'[Type])
    ,ALL('Dimension Product'[Type])
)

VAR __before_suppression =
SUM('Fact Sales'[Amount])

VAR __after_suppression =
IF(
    __product_category_count_filter_context <> __product_category_count_all
    || __product_type_count_filter_context <> __product_type_count_all
    ,BLANK()
    ,__before_suppression
)

RETURN
__after_suppression

[AlB]

@Anonymous 

Yeah, that piece of code looks sound to me. But you can check directly on the data if it does work, right?

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

@AlB 

Yes, the code seems to be giving the desired effect.  I was thinking, though, that the use of ISFILTERED and ISCROSSFILTERED was necessary or to make the the code more efficient.

[AlB]

@Anonymous 

Not really. I just mentioned bit ecause I answered quickly (without going into the details) and in other situations IS(CROSS)FILTERED are useful .They are not necessary in your case. Your code looks fine to me

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers