cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Brotedo
Frequent Visitor

Two Slicers on Same Column, Measures Based on Those Values

I have a simple single table dataset which has columns for Customer, Product, and Sales. I want to compare customers head to head based on sales for different products.

What I've tried so far: I duplicated the Name column twice (to form Name1 and Name2), and have a slicer based on each that are set to ignore each other. 

I'm struggling to create measures to sum sales based on those slicers. This is what I tried:

SalesPick1 = if(HASONEVALUE('Table'[Name1]), CALCULATE(sum('Table'[Sales]), REMOVEFILTERS('Table'[Name2])))
SalesPick2 = if(HASONEVALUE('Table'[Name2]), CALCULATE(sum('Table'[Sales]), REMOVEFILTERS('Table'[Name1])))

Then I have a matrix with rows based on Product, and two value columns, Salespick1 and Salespick2. If I select a name from one of the slicers and leave the other blank, the columns work. If I select different values for the two slicers, I get no data in my matrix.

I want this:

Product        Sales for Pick1        Sales for Pick2

A                   x1                           y1

B                   x2                           y2

C                   x3                           y3

 

 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @Brotedo 

these two columns should be two single column tables. Create inactive relationship between each of these tables with the original column in the original table. Then you can use CALCULATE with USERELATIONSHIP to activate the desired relationship for each measure or variable. 

View solution in original post

1 REPLY 1
tamerj1
Super User
Super User

Hi @Brotedo 

these two columns should be two single column tables. Create inactive relationship between each of these tables with the original column in the original table. Then you can use CALCULATE with USERELATIONSHIP to activate the desired relationship for each measure or variable. 

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.

Top Solution Authors
Top Kudoed Authors