Forum Discussion

dsynan's avatar
dsynan
Advocate III
8 years ago

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 s...
Need Help
v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
8 years ago

Hi dsynan,

 

Suppose your source table is like:

 

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])

 

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. 

 

Best regards,

Yuliana Gu

  • jeffkrop's avatar
    jeffkrop
    Regular Visitor
    8 years ago

    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? 

  • Zain26's avatar
    Zain26
    Advocate II
    8 years ago

    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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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 here

     

    Like 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's avatar
    Anonymous
    Not applicable
    3 years ago

    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 here

     

    Like 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)