Separate Independent Slicers For The Same Column (Field)

Hi Brian,

 

Interesting question 🙂

 

I have attached a sample PBIX showing how I would set this up using your sample data.

 

In cases where you want to compare between values of the same dimension (Customer in this case), I recommend following the general approach discussed here on DAX Patterns.

The DAX Patterns example involves dates, but it can equally apply to any dimension.

 

The basic idea is to:

• Create a second copy of the dimension table used for comparison (Customer in this case). In my sample model, I called this table Comparison Customer.
• Create a 1:1 bidirectional inactive relationship between Customer & Comparison Customer.
• Set up appropriate measures that either:
  • Follow Customer table filters by using the default relationships
  • Or follow the Comparison Customer table filters by activating the inactive relationship and removing filters from Customer. 
  • When required, iterate over the Product dimension for some of the variance analysis logic.

Here is the relationship diagram:

Here are some of the measures:

Revenue = 
SUM ( Sales[Revenue] )

Revenue Comparison = 
CALCULATE (
    [Revenue],
    REMOVEFILTERS ( Customer ),
    USERELATIONSHIP ( Customer[Customer ID], 'Comparison Customer'[Comparison Customer ID] )
)

Slicer 1 Revenue = 
SUMX ( 
    VALUES ( 'Product'[Product] ),
    IF (
        [Slicer 2 Average Price] > 0,
        [Slicer 1 Quantity] * [Slicer 1 Average Price]
    )
)

//etc...

 See the attached PBIX which can hopefully be applied/tweaked for your scenario.

 

Regards,

Owen