Visual filter not actually filtering out values from a measure

tavolo89 — Mon, 27 Mar 2023 16:20:02 GMT

I am trying to use the method outlined in this video from BI Elite to slice on a measure value.

https://www.youtube.com/watch?v=AHiCE1N0XHE

As you can see, the slicer value provided is "80-90" and the value returned from my measure "Ranking_Views_Banding" is "80-90%".

The second column "Filter - View Bands" is a DAX measure:

Filter - View Bands =

VAR CurrentSelection = SELECTEDVALUE('Slice by - View Bands'[View Band])

Var CurrentBand = [Ranking_Views_Banding]

RETURN

IF(CurrentSelection = CurrentBand, "Yes", "No")

So far, so good. However, when I try and filter on the visual to only include "Yes" values from the "Filter - View Bands" measure it still includes the "No" values. I've tried using a switch statement, and using boolean values instead of text. The result is always the same.

It feels like the visual filter is evaluating in a different context perhaps? But I have no idea how to proceed. I would be very grateful if someone can help me understand this.

Re: Visual filter not actually filtering out values from a measure

johnt75 — Tue, 28 Mar 2023 08:33:19 GMT

Use Performance Analyzer to get the DAX code for the table visual and paste it into DAX Studio. There will be a variable with ValueFilter in the name, where it will likely be wrapping a SUMMARIZECOLUMNS inside a FILTER checking that the value of your filter view brands measure is Yes. Take that inner SUMMARIZECOLUMNS and evaluate it, that should give a better understanding of what is going on.

Re: Visual filter not actually filtering out values from a measure

tavolo89 — Tue, 28 Mar 2023 12:03:39 GMT

Thank you. This is the DAX query (with a couple of fields changed to stay anonymous!). I notice that if I evaluate __ValueFilterDM1 then I get the result I'm expecting - i.e. filtered for "Yes" on the "Filter - View Bands" measure.

How can I make PBI apply the same filter to the __DS0PrimaryWindowed variable?

// DAX Query
DEFINE
    VAR __DS0FilterTable =
        TREATAS({"YTD"}, 'Period A'[Is_YTD])

    VAR __DS0FilterTable2 =
        TREATAS({"LW-1"}, 'Period B'[Is_LW-1])

    VAR __DS0FilterTable3 =
        TREATAS({"Filter1"}, 'dim Product'[myAttributeFilter])

    VAR __DS0FilterTable4 =
        TREATAS({"90-100%"}, 'Slice by - View Bands'[View Band])

    VAR __ValueFilterDM1 =
        FILTER(
            KEEPFILTERS(
                SUMMARIZECOLUMNS(
                    'dim Product'[Item ID],
                    'Slice by - View Bands'[View Band],
                    'dim Product'[myAttribute1],
                    __DS0FilterTable,
                    __DS0FilterTable2,
                    __DS0FilterTable3,
                    __DS0FilterTable4,
                    "Ranking_Views_Banding", 'Web Detail'[Ranking_Views_Banding],
                    "v_Ranking_Views_Banding_FormatString", IGNORE('Web Detail'[_Ranking_Views_Banding FormatString]),
                    "Filter___View_Bands", 'Web Detail'[Filter - View Bands],
                    "v_Filter___View_Bands_FormatString", IGNORE('Web Detail'[_Filter - View Bands FormatString]),
                    "Ranking_Views_Percentile", 'Web Detail'[Ranking_Views_Percentile],
                    "v_Ranking_Views_Percentile_FormatString", IGNORE('Web Detail'[_Ranking_Views_Percentile FormatString]),
                    "v__Product_Views", 'Web Detail'[# Product Views],
                    "v___Product_Views_FormatString", IGNORE('Web Detail'[_# Product Views FormatString])
                )
            ),
            [Filter___View_Bands] = "Yes"
        )

    VAR __DS0Core =
        SUMMARIZECOLUMNS(
            ROLLUPADDISSUBTOTAL(
                ROLLUPGROUP(
                    'dim Product'[Item ID],
                    'Slice by - View Bands'[View Band],
                    'dim Product'[myAttribute1]
                ), "IsGrandTotalRowTotal"
            ),
            __DS0FilterTable,
            __DS0FilterTable2,
            __DS0FilterTable3,
            __DS0FilterTable4,
            __ValueFilterDM1,
            "v__Product_Views", 'Web Detail'[# Product Views],
            "Ranking_Views_Banding", 'Web Detail'[Ranking_Views_Banding],
            "v_Ranking_Views_Banding_FormatString", IGNORE('Web Detail'[_Ranking_Views_Banding FormatString]),
            "Filter___View_Bands", 'Web Detail'[Filter - View Bands],
            "v_Filter___View_Bands_FormatString", IGNORE('Web Detail'[_Filter - View Bands FormatString]),
            "Ranking_Views_Percentile", 'Web Detail'[Ranking_Views_Percentile],
            "v_Ranking_Views_Percentile_FormatString", IGNORE('Web Detail'[_Ranking_Views_Percentile FormatString]),
            "v___Product_Views_FormatString", IGNORE('Web Detail'[_# Product Views FormatString])
        )

    VAR __DS0PrimaryWindowed =
        TOPN(
            502,
            __DS0Core,
            [IsGrandTotalRowTotal],
            0,
            [v__Product_Views],
            0,
            'dim Product'[Item ID],
            1,
            'Slice by - View Bands'[View Band],
            1,
            'dim Product'[myAttribute1],
            1
        )

EVALUATE
__DS0PrimaryWindowed
//__ValueFilterDM1

ORDER BY
    //[IsGrandTotalRowTotal] DESC,
    [v__Product_Views] DESC,
    'dim Product'[Item ID],
    'Slice by - View Bands'[View Band],
    'dim Product'[myAttribute1]

Re: Visual filter not actually filtering out values from a measure

johnt75 — Tue, 28 Mar 2023 13:23:16 GMT

Bit of a shot in the dark but can you try commenting out __DS0Filter3 from both __ValueFilterDM1 and __DS0Core? I just wonder if the filter on the product table is interfering somehow.

topic Re: Visual filter not actually filtering out values from a measure in DAX Commands and Tips

Visual filter not actually filtering out values from a measure

Re: Visual filter not actually filtering out values from a measure

Re: Visual filter not actually filtering out values from a measure

Re: Visual filter not actually filtering out values from a measure