Set Slicer to only work on 1 data table

[Topjacket]

Hi All

I am try to build a column chart that shows the record count for a single customer (selected by slicer) vs the average record count for a group of customers (selected by a different slicer).

The only way I could think to do this was to copy the data table so I have 2 versions of it, I could then apply the slicers to the individual data sets but I can't see a way of restricting the slicer to only look at one data table.

Is there a way to restrict a slicer to only look at one data table?

Is there a better/easier way to go about achieving this?

Thanks in advance for any help

[PaulDBrown]

You can share the PBIX file via a cloud service (OneDrive, Google Drive, Deopbox....)

[Topjacket]

Thanks again.

Have had a bit of trouble getting a sharable link due to company restrictions. I have pasted a link below.

https://drive.google.com/file/d/13_per3vcB2Jrd7aLg2216tKE7Algx4CX/view?usp=drivesdk

Cheers

[PaulDBrown · Accepted solution]

Thanks for the file. See if this works for you. You only need one call table; you need two dimension tables for customer. I've called one Dim Customer and the other Group Customer. Create relationships between these tables and the call table via the customer fileds. Leave the Group Customer relationship inactive. The model looks like this:

Now create the measures:

Call Count = 
SUM('Customer Calls'[number])

Average count by Group = 
AVERAGEX('Customer Group', 
CALCULATE (
    [Call Count],
    REMOVEFILTERS ( 'Dim Customer'[Customer Name] ),
    USERELATIONSHIP ( 'Customer Group'[Customer Name], 'Customer Calls'[Customer Name] )))

If you want the selecion in the slicers to be mutually exclussive, you can use this measure as a filter for the Customer group Slicer:

Filter GSlicer = 
COUNTROWS(
    EXCEPT('Customer Group', 'Dim Customer'))

and this measure as a filter for the customer slicer:

Filter Dim Slicer =
IF (
    ISFILTERED ( 'Customer Group'[Customer Name] ),
    COUNTROWS ( EXCEPT ( 'Dim Customer', 'Customer Group' ) ),
    1
)

To get

 I've attached the sample file

[Topjacket]

Thank you very much for this. Its brilliant!!

Its doing exaclty what I need.

I'm going to read up on the dimention bit as thats all new to me.

[PaulDBrown]

To make the visual clearer, you can create a measure to use as a title with:

Visual Title = "Count of " & SELECTEDVALUE(Dim Customer [Customer Name]) & " compared to the average count of  " & CONCATENATEX(VALUES(Customer Group [Customer Name]), Customer Group [Customer Name], ", ")

Select the visual, go to Title under General in the formatting pane, select the fx icon and under value select [Visual Title] measure

[PaulDBrown]

A Dimension table is a table containing  unique values from a field in your fact table(s) with a relationship with the corresponding field in your fact table(s). They make the model more efficient among other benefits. They can be created in Power Query or using DAX. I created them using DAX with the DISTINCT function. (See the code in the sample file). 
they are extremely useful and a recommended best practice.

[Topjacket]

Thanks again Paul, this is really useful.

[PaulDBrown]

Can you show a depiction of the model and provide sample data or PBIX file?

[Topjacket]

Hi Paul

Thanks for your quick reply, I have built a very basic mock up of what I am trying to do. I need the slicers to work against the individual columns in the chart so I can compare the 2 values but I can't see a way of making them work independently. I have just seen that I can't upload a pbix so have added some pictures. Please let me know if you need more information.

I hope this makes sense.

Thanks again