ISFILTERED function against target column doesn't work as expected when there are other filters

[bergen288]

I have the following measure to be used as a title of table visual:  the target table/column is 'PBI_XZ_Fraud_Alerts'[Hour] which is also defined as a slicer as shown in 1st screenshot.  In addition, there are 2 filter conditions defined as shown in screenshot 2.  As the result, my __COND is always TRUE even if the Hour slicer is cleared.  If I clear those 2 filter conditions in screenshot 2, __COND will be FALSE.  Unfortunately, those 2 filters are required to report yesterday's data only in this page.  What's the best way to address my issue?

Thanks.

Yesterday Hourly Title =

    VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT('PBI_XZ_Fraud_Alerts'[Hour])

    VAR __MAX_VALUES_TO_SHOW = 3

    VAR __COND = ISFILTERED('PBI_XZ_Fraud_Alerts'[Hour])

    VAR __Start_string = "ISFILTER CONDITION IS: " & __COND

    // VAR __Start_string =" Yesterday's Payments Amount in Descending Order "

    RETURN

    IF(

        __COND,

        IF(

            __DISTINCT_VALUES_COUNT > __MAX_VALUES_TO_SHOW,

            __Start_string & "at Hour " &

            CONCATENATE(

                CONCATENATEX(

                    TOPN(

                        __MAX_VALUES_TO_SHOW,

                        VALUES('PBI_XZ_Fraud_Alerts'[Hour]),

                        'PBI_XZ_Fraud_Alerts'[Hour],

                        ASC

                    ),

                    'PBI_XZ_Fraud_Alerts'[Hour],

                    ", ",

                    'PBI_XZ_Fraud_Alerts'[Hour],

                    ASC

                ),

                ", etc."

            ),

            __Start_string & "at Hour " &

            CONCATENATEX(

                VALUES('PBI_XZ_Fraud_Alerts'[Hour]),

                'PBI_XZ_Fraud_Alerts'[Hour],

                ", ",

                'PBI_XZ_Fraud_Alerts'[Hour],

                ASC

            )

        )

        , __Start_string & "for All Hours"

    )

[andrewpirie · Accepted solution]

I was able to reproduce this when using a measure as the visual-level filter. Just having a visual-level filter based on a measure seemed to switch my ISFILTERED(Alerts[Alert Hour]) to true even though there were no direct filters against that column. Even a basic measure like "RETURN 1" with a visual-level filter on that measure and including Alerts[Alert Hour] as a column in the visual made the title switch to filtered.

If you are using the [Alert Hour] column from the fact table, you could resolve this by adding an Alert Hours dimension table with a 1 to many relationship to the Alerts[Alert Hour] column, and using that dimension table for slicing and checking ISFILTERED within the title measure, while keeping the Alerts[Alert Hour] column as the column in the visual.

A possible workaround to your issue is that you could also get the number of total possible alert hours selected and compare the visible values to that instead of using ISFILTERED, if that is feasible in your situation. This would show as being filtered if the visible data didn't include alerts for all hours, so it's not ideal.

Instead of VAR __COND = ISFILTERED('Alerts'[Alert Hour]) this would be:

VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT('Alerts'[Alert Hour])

VAR __DISTINCT_VALUES_ALLSELECTED = CALCULATE(DISTINCTCOUNT(Alerts[Alert Hour]), ALLSELECTED(Alerts[Alert Hour]))

VAR __COND = __DISTINCT_VALUES_COUNT <> __DISTINCT_VALUES_ALLSELECTED

[bergen288]

Per your suggestion, I created dimentional Hour table and established 1:* relationship with my fact table.  Then, the 'Hour'[Hour] is used in the slicer and below is my new meaure.  It works as expected as shown in screenshot below.  

Thanks a lot.

Yesterday Hourly Title =

    VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT('Hour'[Hour])

    VAR __MAX_VALUES_TO_SHOW = 3

    VAR __COND = calculate(ISFILTERED('Hour'[Hour]) , allselected())

    VAR __Start_string = "ISFILTER CONDITION IS: " & __COND

    // VAR __Start_string =" Yesterday's Payments Amount in Descending Order "

    VAR __LIST =

            IF(

            __DISTINCT_VALUES_COUNT > __MAX_VALUES_TO_SHOW,

            __Start_string & "at Hour " &

            CONCATENATE(

                CONCATENATEX(

                    TOPN(

                        __MAX_VALUES_TO_SHOW,

                        VALUES('Hour'[Hour]),

                        'Hour'[Hour],

                        ASC

                    ),

                    'Hour'[Hour],

                    ", ",

                    'Hour'[Hour],

                    ASC

                ),

                ", etc."

            ),

            __Start_string & "at Hour " &

            CONCATENATEX(

                VALUES('Hour'[Hour]),

                'Hour'[Hour],

                ", ",

                'Hour'[Hour],

                ASC

            )

        )

    RETURN

    IF(

        __COND,

        __LIST,

        __Start_string & " for All Hours"

    )

[amitchandak]

@bergen288 , change it like and try

VAR __COND = calculate( ISFILTERED('PBI_XZ_Fraud_Alerts'[Hour]) , allselected())

[bergen288]

I tried it, but it didn't work.