Behavior of ALLSELECTED() within Visuals when no slicer is selected

[lucasN]

Hi PBI Community,

First of all: You can find a copy of the pbix file here Link Sample Data 

Second of all: I suspect that my problem is quite a basic one revolving around the correct use of Filter contexts, ALLSELECTED() and maybe data lineage. Nevertheless I'll give you all the information below:

I am working on a dynamic ABC-Analysis with some test data following the dax pattern on https://www.daxpatterns.com/abc-classification/. More specifically I want to dynamically calculate the number of products in each class (A,B,C,D) depending on the selected company and year. The simple data model looks like this:

My problem does not concern the logic behind the ABC measure but how it is visualized as seen below:

On the right side you can see the desired behavior, where I use a slicer which filters the table, showing me the correct number of products per class.

But when I try to replicate this behaviour in a visual without a slicer (see Matrix and stacked bar chart on the left) the filter on company or year for that matter is not taken into account.
The desired behavior would be that the bar chart would show the same numbers for Company 1 as the table on the right.

Edit: To clarify: I want the visual to calculate the correct numbers WITHOUT input from the slicers. That is why I disabled the interaction between the two objects.

The measure in question is "# Products" and only adds small changes compared to its original on daxpatterns.com.

# Products = 
VAR ProdSales =
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE ( Sales, 'Product'[ProductKey] ),
            "@Sales", [Sales Amount]
        ),
        
        ALLSELECTED ( 'Company'), //  Changed this line from original
        ALLSELECTED('Date') //  Changed this line from original
    )

// Calculate the sum of sales within the selected filter context
VAR AllSales =
    CALCULATE (
        [Sales Amount],
        ALLSELECTED ( 'Company'), //  Changed this line from original
        ALLSELECTED('Date') //  Changed this line from original
    )
VAR ProdSalesPerc =
    ADDCOLUMNS (
        ProdSales,
        "@AggSales%",
        VAR CurrentSalesAmt = [@Sales]
        
        // Only sum products with higher total sales. Neet trick to skip sorting.
        // Adds the percentage column to divide products into category
        
        VAR CumulatedSales =
            SUMX (
                FILTER (
                    ProdSales,
                    [@Sales] >= CurrentSalesAmt
                ),
                [@Sales]
            )
        VAR Perc = 
            DIVIDE (
                CumulatedSales,
                AllSales
            )
        RETURN
            MIN ( Perc, 1 ) -- Avoid >100% in case of rounding issues
    )
VAR ProductsInClass =
    FILTER (
        CROSSJOIN ( // join each product with all of their ABC-classes and their boundaries
            ProdSalesPerc,
            'ABC Classes'
        ),
        AND ( // Filter the cross join product based on boundaries
            [@AggSales%] > 'ABC Classes'[Lower Boundary],
            [@AggSales%] <= 'ABC Classes'[Upper Boundary]
        )
    )
VAR Result =
    CALCULATE (
        COUNTROWS ( 'Product' ),
        // Filter results from visual e.g. table with class in each row A, B, C
        // The filter on ProductKey in ProductsInClass also applies to Product
        KEEPFILTERS ( ProductsInClass )
    )
RETURN
    Result

I expect the problem to be either the ALLSELECTED() function within the variable ProdSales at the top of the code block or the KEEPFILTERS() function within Result at the bottom. Unfortuantely this surpasses my basic filter DAX knowledge 

Any adivice would be gladly appreciated.
Thank you!

Lucas
PS.: Should I find the solution myself in the meantime, I'll post it here.

[v-yueyunzh-msft]

Hi , @lucasN 

Thanks for your sample .pbix file! I check in my side and i find that you close the visual interaction , if you want to open it , you can click "Edit interactions" in "Format" tab:

Then the slicer filter will be applied to the visuals you want to .

For more information, you can refer to :
Change how visuals interact in a report - Power BI | Microsoft Learn

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[lucasN]

Thanks for taking the time to look at my file @v-yueyunzh-msft . Unfortuantely filter interactions are not the issue here. It seems that I wasn't precise enough with my question ( I clarified it in the post now).

I disabled interactions between the slicer and the visual on purpose as I want the visual to show the correct numbers without any input from the slicers.

I found a solution in the meantime but I am not sure if I understood it correctly:

It seemed that ALLSELECTED() was deleting the filtering done by the visual (e.g. x-axis) when no slicer was selected. However, these filters are necessary for dynamic ABC-filtering by dimesnions such as company or year. Therefore I replaced the ALLSELECTED() functions with KEEPFILTERS() at the beginning of the DAX code:

# Products = 
VAR ProdSales =
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE ( Sales, 'Product'[ProductKey] ),
            "@Sales", [Sales Amount]
        ),
        
        KEEPFILTERS( 'Company'), // ❗ instead of ALLSELECTED()
        KEEPFILTERS('Date') //  ❗ instead of ALLSELECTED()
    )

// Calculate the sum of sales within the selected filter context
VAR AllSales =
    CALCULATE (
        [Sales Amount],
        KEEPFILTERS('Company'), // ❗ instead of ALLSELECTED()
        KEEPFILTERS('Date') //  ❗ instead of ALLSELECTED()
    )

...

This did the trick and it appears that the calculated results are correct. 
Still I haven't understood completly why it worked. If anybody could enlighten me and/or tell me if this approach is correct/ best practice I would be very grateful. 
Best regards,
Lucas

[v-yueyunzh-msft · Accepted solution]

Hi , @lucasN 

Thanks for your quick response and detailed explanation for your question!

First, in your question, you can not use the ALLSELECTED() function because it ignore the all filter context based on the table or the column. Even though you put the field on the visual as a row header , it will also be ignored( The context filter of the ALLSELECTED() function is just the slicer filter in Power BI Desktop ). So , you need to replace the ALLSELETCED() function with the VALUES() and then we can solve your problem.

# Products 2 =

VAR ProdSales =

    CALCULATETABLE (

        ADDCOLUMNS (

            SUMMARIZE ( Sales, 'Product'[ProductKey] ),

            "@Sales", [Sales Amount]

        ),

       

       VALUES('Company'[Company]), // ❗ Changed this line from original

       VALUES('Date'[Year]) // ❗ Changed this line from original

    )



// Calculate the sum of sales within the selected filter context

VAR AllSales =

    CALCULATE (

        [Sales Amount],

        VALUES('Company'[Company]), // ❗ Changed this line from original

        VALUES('Date'[Year]) // ❗ Changed this line from original

    )

VAR ProdSalesPerc =

    ADDCOLUMNS (

        ProdSales,

        "@AggSales%",

        VAR CurrentSalesAmt = [@Sales]

       

        // Only sum products with higher total sales. Neet trick to skip sorting.

        // Adds the percentage column to divide products into category

       

        VAR CumulatedSales =

            SUMX (

                FILTER (

                    ProdSales,

                    [@Sales] >= CurrentSalesAmt

                ),

                [@Sales]

            )

        VAR Perc =

            DIVIDE (

                CumulatedSales,

                AllSales

            )

        RETURN

            MIN ( Perc, 1 ) -- Avoid >100% in case of rounding issues

    )

VAR ProductsInClass =

    FILTER (

        CROSSJOIN ( // join each product with all of their ABC-classes and their boundaries

            ProdSalesPerc,

            'ABC Classes'

        ),

        AND ( // Filter the cross join product based on boundaries

            [@AggSales%] > 'ABC Classes'[Lower Boundary],

            [@AggSales%] <= 'ABC Classes'[Upper Boundary]

        )

    )

VAR Result =

    CALCULATE (

        COUNTROWS ( 'Product' ),

        // Filter results from visual e.g. table with class in each row A, B, C

        // The filter on ProductKey in ProductsInClass also applies to Product

        KEEPFILTERS ( ProductsInClass )

    )

RETURN

    Result

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[lucasN]

Thank you very much @v-yueyunzh-msft! That did the trick. In hindsight it is so obvious 😅
I will change the title of the post to better reflect that the main problem was the behavior of ALLSELECTED within visuals
All the best!