Power BI filter propagation playground report

If you sometimes find yourself scratching your head over DAX functions like ISFILTERED, ISCROSSFILTERED, ISINSCOPE, and HASONEFILTER, fear not! I have a surprise for you. I’ve prepared a dummy .pbix file (download here) that lets you dive into the world of these four functions. You can play around with them, choose different dimension attributes to perform checks on, apply data slicers, cross filter visuals and even decide which attributes make it to the matrix rows. So, have fun playing around with it! 🤣

super simplistic semantic model

While there may not be any groundbreaking innovations within this report, it does offer an opportunity to experiment and discover the outcomes for yourself.

One notable aspect of the report is the Functions calculation group, which allows you to specify the dimension column over which you wish to execute the four aforementioned DAX functions.

Overall, the report serves as a hands-on tool for familiarizing yourself with the behavior of ISFILTERED, ISCROSSFILTERED, ISINSCOPE, and HASONEFILTER.

---------------------------------
-- Calculation Group: 'Functions'
---------------------------------
CALCULATIONGROUP Functions[Function Name]
    Visible = FALSE

    CALCULATIONITEM "ISINSCOPE" = 
        IF(
            SELECTEDMEASURENAME( ) = "Dummy Measure",
            SWITCH(
                SELECTEDVALUE( attribute_switch[attribute] ),
                "brand", IF( ISINSCOPE( prod_dim[brand] ), "ISINSCOPE (brand)" ),
                "category", IF( ISINSCOPE( prod_dim[category] ), "ISINSCOPE (category)" ),
                "country", IF( ISINSCOPE( country_dim[country] ), "ISINSCOPE (country)" ),
                "continent", IF( ISINSCOPE( country_dim[continent] ), "ISINSCOPE (continent)" )
            ),
            SELECTEDMEASURE()
        )
        Ordinal = 0

    CALCULATIONITEM "ISFILTERED" = 
        IF(
            SELECTEDMEASURENAME( ) = "Dummy Measure",
            SWITCH(
                SELECTEDVALUE( attribute_switch[attribute] ),
                "brand", IF( ISFILTERED( prod_dim[brand] ), "ISFILTERED (brand)" ),
                "category", IF( ISFILTERED( prod_dim[category] ), "ISFILTERED (category)" ),
                "country", IF( ISFILTERED( country_dim[country] ), "ISFILTERED (country)" ),
                "continent", IF( ISFILTERED( country_dim[continent] ), "ISFILTERED (continent)" )
            ),
            SELECTEDMEASURE( )
        )
        Ordinal = 1

    CALCULATIONITEM "ISCROSSFILTERED" = 
        IF(
            SELECTEDMEASURENAME( ) = "Dummy Measure",
            SWITCH(
                SELECTEDVALUE( attribute_switch[attribute] ),
                "brand", IF( ISCROSSFILTERED( prod_dim[brand] ), "ISCROSSFILTERED (brand)" ),
                "category", IF( ISCROSSFILTERED( prod_dim[category] ), "ISCROSSFILTERED (category)" ),
                "country", IF( ISCROSSFILTERED( country_dim[country] ), "ISCROSSFILTERED (country)" ),
                "continent", IF( ISCROSSFILTERED( country_dim[continent] ), "ISCROSSFILTERED (continent)" )
            ),
            SELECTEDMEASURE()
        )
        Ordinal = 2

    CALCULATIONITEM "HASONEFILTER" = 
        IF(
            SELECTEDMEASURENAME( ) = "Dummy Measure",
            SWITCH(
                SELECTEDVALUE( attribute_switch[attribute] ),
                "brand", IF( HASONEFILTER( prod_dim[brand] ), "HASONEFILTER (brand)" ),
                "category", IF( HASONEFILTER( prod_dim[category] ), "HASONEFILTER (category)" ),
                "country", IF( HASONEFILTER( country_dim[country] ), "HASONEFILTER (country)" ),
                "continent", IF( HASONEFILTER( country_dim[continent] ), "HASONEFILTER (continent)" )
            ),
            SELECTEDMEASURE()
        )
        Ordinal = 3

attribute_switch table

Don’t forget to subscribe to

👉 Power BI Publication

👉 Power BI Newsletter

and join our Power BI community

👉 Power BI Masterclass