cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dsynan
Advocate III
Advocate III

Compare values based on two slicer selections - using only the locations where both selections match

I have one table. Would like to select two products from the table to compare some results (yield, profit, etc.), but only want the results for locations that have both products. 

 

1. How to add slicers so two different values from the same table can be selected at the same time?

2. How to filter the results down to only locations that have both products?

6 REPLIES 6
v-yulgu-msft
Microsoft
Microsoft

Hi @dsynan,

 

Suppose your source table is like:

1.PNG

 

Please create two extra tables which are unrelated to source table. Later, you should drag fields (Slicer1[Product] and Slicer2[Product]) from these two tables into two separate slicers.

Slicer1 = VALUES(Product_sales[Product]) 
Slicer2 = VALUES(Product_sales[Product]) 

2.PNG3.PNG

 

Create below measures:

Sum product for slicer1 =
CALCULATE (
    SUM ( Product_sales[Sales] ),
    FILTER (
        Product_sales,
        Product_sales[Product] = LASTNONBLANK ( Slicer1[Product], 1 )
    )
)

Sum product for slicer2 =
CALCULATE (
    SUM ( Product_sales[Sales] ),
    FILTER (
        Product_sales,
        Product_sales[Product] = LASTNONBLANK ( Slicer2[Product], 1 )
    )
)

Isblank for slicer1 = IF([Sum product for slicer1]=BLANK(),0,1) 
Isblank for slicer2 = IF([Sum product for slicer2]=BLANK(),0,1) 

Add [Sum product for slicer1] and [Sum product for slicer1] to table visual. Add [Isblank for slicer1], [Isblank for slicer2] to "visual level filters", set their values to 1. 

4.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Team 

 

I need help in same where I want to add two more column in Slicer 1 and 2

Slicer1 = VALUES(Product_sales[Product]) 
Slicer2 = VALUES(Product_sales[Product]) 

 And then I slice the value as per category and sub category

I want add two more columns hereI want add two more columns here

 

Like this and then I can select value as per category and sub categoryLike this and then I can select value as per category and sub category

 

What will be Dax below

Sum product for slicer1 =
CALCULATE (
    SUM ( Product_sales[Sales] ),
    FILTER (
        Product_sales,
        Product_sales[Product] = LASTNONBLANK ( Slicer1[Product], 1 )
    )
)

Sum product for slicer2 =
CALCULATE (
    SUM ( Product_sales[Sales] ),
    FILTER (
        Product_sales,
        Product_sales[Product] = LASTNONBLANK ( Slicer2[Product], 1 )
    )
)

Isblank for slicer1 = IF([Sum product for slicer1]=BLANK(),0,1) 
Isblank for slicer2 = IF([Sum product for slicer2]=BLANK(),0,1)

 

Hi Team 

 

I need help in same where I want to add two more column in Slicer 1 and 2

Slicer1 = VALUES(Product_sales[Product]) 
Slicer2 = VALUES(Product_sales[Product]) 

 And then I slice the value as per category and sub category

I want add two more columns hereI want add two more columns here

 

Like this and then I can select value as per category and sub categoryLike this and then I can select value as per category and sub category

 

What will be Dax below

Sum product for slicer1 =
CALCULATE (
    SUM ( Product_sales[Sales] ),
    FILTER (
        Product_sales,
        Product_sales[Product] = LASTNONBLANK ( Slicer1[Product], 1 )
    )
)

Sum product for slicer2 =
CALCULATE (
    SUM ( Product_sales[Sales] ),
    FILTER (
        Product_sales,
        Product_sales[Product] = LASTNONBLANK ( Slicer2[Product], 1 )
    )
)

Isblank for slicer1 = IF([Sum product for slicer1]=BLANK(),0,1) 
Isblank for slicer2 = IF([Sum product for slicer2]=BLANK(),0,1)

 

Anonymous
Not applicable

Hi! Great solutions. Is there a way to make it possible to select several product in each of the slicers, and make the measure sum up all selected? Thanks!

Hi @v-yulgu-msft
I was working on a very similar problem. Just needed to ask if there is any alternative to LastNonBlank() ? Using LastNonBlank results in a difference in values when slicer is set to All. 
I have another table which I am using to compare the results and there are some Products against which the Sum is different in this comparison method. 
Any help would be highly appreciated. 

This is great information, I am wondering if there is a way to make a table that shows the information where they are alike in the two slicers? 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.